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

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

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  Reply With Quote

2. Does this work?

Code:
`=LOOKUP(10^10,SEARCH(\$C\$1:\$C\$4,A1),D1:D4)`  Reply With Quote

3. Yes, that worked perfectly. Thank you NBVC.

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

Thanks again.  Reply With Quote

4. 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 4 entry.  Reply With Quote

asterisk, excel function, table array, vlookup, wild card 