# Wild Card

#### Peter Muller

##### Board Regular
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.

 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

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### jasonb75

##### Well-known Member
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

##### Board Regular
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

##### Well-known Member
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

##### Board Regular
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

##### Board Regular
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

Replies
3
Views
79
Replies
0
Views
56
Replies
3
Views
100
Replies
0
Views
418
Replies
4
Views
88