How to pick up formula from differentcell based on criteria

[rickf19]

Hi
hopefully an easy one for those of you in the know
I want cell c4 to cell C end of data
If cell L3 has value CBP Then return formula in cell C1 to cell c4
If cell L4 has value DTZ Then return formula in cell C2 to cell c4

The formula in cells c1 and c2 are vlookups which currently have #N/A values but work if I copy and paste formulas to cells in Col C as the data they need is in other columns within the data
Just looking to see if I can streamline the process a bit


Thanks
Rick

 

[jasonb75]

I would suggest 2 vlookups nested in IF, or similar.

Best guess without the original formulas.

=IF(L3="CBP",vlookup from C1,IF(C$="DTZ",vlookup from C2,""))

What if L3="CBP" and L4="DTZ"? Which takes priority?

 

[rickf19]

thanks for reply

CBP lookup is CellC1 DTZ lookup is cell C2


Here is formula based on your reply, returns #VALUE .
The value should be the return from the look up
=IF($L1133="CBP",VLOOKUP($E1133,'Code Map'!$J$2:$AA$162,17,FALSE)),IF($L1133="DTZ",VLOOKUP($E1133,'Code Map'!$N$2:$AA$162,13,FALSE))

Any help appreciated

Thanks again
Rick

 

[jasonb75]

You have a bracket in the wrong place and are missing the "" at the end.

Having seen the actual vlookups, I would suggest a different approach. This assumes that CBP and DTZ are the only options in column L, anything else would return an error / no match.

=IFERROR(INDEX('Code Map'!$AA$2:$AA$162,MATCH($E1133,INDEX('Code Map'!$J$2:$N$162,0,IF($L1133="CBP",1,5)),0)),"No Match")

If there are other options in column L then I will make another slight change to prevent false matches if the content of column E is still found in the code map.

 

[rickf19]

will test that out
there may be other values in column L but they will not necessarily need a look up and DTZ and CBR will be the bulk of the references

Thanks again
Rick

 

[jasonb75]

In that case, a slight edit. This will eliminate any false matches where the content of column E is found in the lookup table but column L is not equal to either criteria.
Not tested but should be correct.

=IFERROR(INDEX('Code Map'!$AA$2:$AA$162,IF($L1133="CBP",MATCH($E1133,'Code Map'!$J$2:$J$162,0),IF($L1133="DTZ",MATCH($E1133,'Code Map'!$N$2:$N$162,0),NA()))),"No Match")

 

[rickf19]

=IFERROR(INDEX('Code Map'!$Z$2:$Z$162,IF($L1201="CBP",MATCH($E1201,'Code Map'!$J$2:$J$162,0),IF($L1201="DTZ",MATCH($E1201,'Code Map'!$N$2:$N$162,0),NA()))),"No Match")

This works thanks for help much appreciated
Rick