How Can I Use a Wild Card in a VLOOKUP Table Array?

brnielsen44

New member
Joined
Aug 29, 2014
Messages
2
Reaction score
0
Points
0
I understand how to use a asterisk in a VLOOKUP function's "lookup_value" but I need to work it a little backwards and use a wild card in the "table_array" somehow.

Example:

Cell A1
c.b.a

Cells C1 : C3
a.*.*
b.*.*
c.a.*
c.*.a

Cells D1 : D3
Group 1
Group 2
Group 3
Group 4

I need Cell B1 to vlookup what's in A1 ("c.b.a") and return Group 4.

I have about a thousand values in column A that need to each be assigned one of 50 groups (all in column D). Is there a way to use the wildcard in this backwards function?

Thanks,
brnielsen44
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Does this work?

Code:
=LOOKUP(10^10,SEARCH($C$1:$C$4,A1),D1:D4)[/B]
 

brnielsen44

New member
Joined
Aug 29, 2014
Messages
2
Reaction score
0
Points
0
Yes, that worked perfectly. Thank you NBVC.

Quick follow-up... what does the 10^10 do in that function?

Thanks again.
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
It's a very large number (10 to the power of 10). LOOKUP(looks for that number, and if it does not find it, it will take the last number in the range, which is smaller than that very large number).

SEARCH() yields numeric positional results where matches are found, and LOOKUP then finds the last number in the string of results and returns that number and corresponding D1:D4 entry.
 
Top