Multiple IFs=Vlookups. Is there a shorter way of doing it?

[zx6roo]

I have the below formulae on a tab called Tracking Sheet. It current has 3 levels but I need to push it out to 15 levels as I have separate choices.


=IF(H1="Unit 11",VLOOKUP(A1,Unit11,1,FALSE),IF(H1="Unit 13",VLOOKUP(A1,Unit13,1,FALSE),IF(H1="Unit 14",VLOOKUP(A1,Unit14,1,FALSE),"No")))


H1 equals text from a drop down saying unit 11, Unit 12, Unit 13, etc.
A1 equals a concatenated field from other columns on tab Tracking Sheet.
Unit11, Unit12, Unit13 are name ranges on their respective tab.

Instead of building it out this way for all 15 levels is there a shorter way of doing it with INDEX MATCH or something else? Basically what I am doing is looking at a field on one tab and showing is it exists on any of the other tabs or not.

 

[BarryL]

maybe?

=iferror(vlookup(a1,indirect(Substitute(H1,"Unit ","Unit")&"!q:q"),1,0),"")

 

[zx6roo]

Wait I have it !
=IFERROR(VLOOKUP(A1,INDIRECT(TRIM(SUBSTITUTE(H14,CHAR(32),""))),1,0),"No")

You guys are legends ! Thank you

 

[inactiveuserps07]

Great!

 

[BarryL]

Glad I could help.