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]

if its a proper named range then just

=IFERROR(VLOOKUP(A1,INDIRECT(H1),1,0),"")

 

[inactiveuserps07]

It seems to me that a fairly short formula would cover everything, if I'm understanding you correctly:

=IFERROR(VLOOKUP(A1,INDIRECT(H1&""),1,0),"")

If this is going into one cell only, it should work as you see it above.

If you're copying down a column, just make sure that you add dollar signs to addresses that will be stationary. For instance, if H1 will always be the single drop-down menu for all rows, the formula would look like this:

=IFERROR(VLOOKUP(A1,INDIRECT($H$1&""),1,0),"")

Likewise, if A1 is ONE solitary entry field that never changes, such as on a search form, it would look like this:


=IFERROR(VLOOKUP($A$1,INDIRECT($H$1&""),1,0),"")

 

[inactiveuserps07]

Barry, INDIRECT expects a reference as a text string only, so your formula (I believe) would need to add a small portion:

=IFERROR(VLOOKUP(A1,INDIRECT(H1&""),1,0),"")

Yours was shorter by a bit than my original which used IF(ISERROR()). Good thinking.

 

[BarryL]

Barry, INDIRECT expects a reference as a text string only, so your formula (I believe) would need to add a small portion:

=IFERROR(VLOOKUP(A1,INDIRECT(H1&""),1,0),"")

Yours was shorter by a bit than my original which used IF(ISERROR()). Good thinking.

unit1, unit2, unit 3 is all text. I tested my formula and it worked.

 

[zx6roo]

Cell A1 contains numbers such as 123456WP12345678. That is what I'm looking for across the other tabs.
I've tried it but the result was blank where it should be populated.

So it would be like this:
Tab: Tracking Sheet
A1........................................B1.........................................H1
123456WP00000001...............formula result..................Unit 11
A2........................................B2.........................................H2
123456WP00000002...............formula result..................Unit 13

Tab: Unit 11 - Q:Q name range 'Unit11'
Q10
123456WP00000001

Tab: Unit 13 - Q:Q name range 'Unit13'
Q27
123456WP00000002

 

[BarryL]

=iferror(vlookup(a1,indirect(h1&"!q:q"),1,0),"")

 

[zx6roo]

If I remove the spaces in the drop down list for column H the formula will work, however I need to leave the spaces in there which means they don't match exactly to the name ranges.

 

[inactiveuserps07]

Use TRIM(H1) in the same formula, which will use only the non-space portion of the entry (i.e., the name without whatever spaces you've added).

 

[zx6roo]

Use TRIM(H1) in the same formula, which will use only the non-space portion of the entry (i.e., the name without whatever spaces you've added).

Excellent idea Erik except TRIM doesn't work for single mid spaces

I tried TRIM(SUBSTITUTE(H1,CHAR(160),CHAR(32))) but that didn't work either.