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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,594
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,594
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,794
Messages
5,574,334
Members
412,587
Latest member
Krucial155
Top