Wild Card

[Peter Muller]

Good afternoon Experts,



I am battling to use the wild card in this “if” and “vlookup” formula.



I have in excess of a 2000 entries and 8 sheets.



Please help.

Ref	Name	Designation
MAN1	=IF(C2="Alb 1",VLOOKUP(A2,Albany!A:B,2,FALSE), IF(C2="Cap 1", VLOOKUP(A2,Cape!A:B,2,FALSE)))	Alb 1-Gr C
MAN2		Alb 1-Gr D
MAN3	There are 8 nested "if" statements - 8 different sheets	Alb 1-Gr E
MAN4	How to use the wild card with "Alb 1" , "Cap 1" , etc.	Cap 1-Gr C
MAN5		Cap 1-Gr D
MAN6		Cap 1-Gr E

 

[jasonb75]

You could do it with INDIRECT and a second table, or perhaps with something like

=VLOOKUP(A2,CHOOSE(MATCH(LEFT(C2,3),{"Alb","Cap"},0),Albany!A:B,Cape!A:B),2,0)

 

[Peter Muller]

You could do it with INDIRECT and a second table, or perhaps with something like

=VLOOKUP(A2,CHOOSE(MATCH(LEFT(C2,3),{"Alb","Cap"},0),Albany!A:B,Cape!A:B),2,0)

Thank you,

Will get back to my spreadsheet in an hour, will check and let you know. How will the second table and indirect wok? is there a way around the 8 nested if functions? vba is great if possible

 

[jasonb75]

8 nested ifs only applies if you're still using excel 2003 (or older), with newer versions it is much higher, although there is a kind of unwritten theory that if you need more than 3 or 4 ifs then you're doing it wrong ?

With the second table and indirect, you would list 'Alb', 'Cap', etc in the left column and 'Alberta', 'Cape', etc in the second column (the second column should list the names of the sheets).

Then the formula would be

=VLOOKUP(A2,INDIRECT("'"&VLOOKUP(LEFT(C2,3),$D$2:$E$10,2,0)&"'!A:B"),2,0)

Where $D$2:$E$10 refers to the second table.

 

[Peter Muller]

Thank you,

Will get back to my spreadsheet in an hour, will check and let you know. How will the second table and indirect wok? is there a way around the 8 nested if functions? vba is great if possible

Thank you very much Jason, this one solves my problem ?

 

[Peter Muller]

8 nested ifs only applies if you're still using excel 2003 (or older), with newer versions it is much higher, although there is a kind of unwritten theory that if you need more than 3 or 4 ifs then you're doing it wrong ?

With the second table and indirect, you would list 'Alb', 'Cap', etc in the left column and 'Alberta', 'Cape', etc in the second column (the second column should list the names of the sheets).

Then the formula would be

=VLOOKUP(A2,INDIRECT("'"&VLOOKUP(LEFT(C2,3),$D$2:$E$10,2,0)&"'!A:B"),2,0)

Where $D$2:$E$10 refers to the second table.

And this one is even better, so thank you very much