Wild Card

Peter Muller

Board Regular
Joined
Oct 15, 2018
Messages
89
Office Version
  1. 2016
Platform
  1. Windows
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.



RefNameDesignation
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
MAN2Alb 1-Gr D
MAN3There are 8 nested "if" statements - 8 different sheetsAlb 1-Gr E
MAN4How to use the wild card with "Alb 1" , "Cap 1" , etc.Cap 1-Gr C
MAN5Cap 1-Gr D
MAN6Cap 1-Gr E
 

Some videos you may like

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
Joined
Dec 30, 2008
Messages
11,459
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 15, 2018
Messages
89
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
11,459
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 15, 2018
Messages
89
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Oct 15, 2018
Messages
89
Office Version
  1. 2016
Platform
  1. Windows
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 (y) :)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,279
Messages
5,571,286
Members
412,375
Latest member
BRJoeyMelo
Top