MS Excel wildcard lookup

问题:

I have a lookup table in Excel with ColA and ColB :

ColA    ColB   ColC
va       1     value1

ColC is the actual value table

I need to do a wildcard lookup with lookup_value = "value1" I need to perform an operation that if the value of ColC matches the characters in ColA then it should return me ColB value, i.e. 1

I want to use wildcards in the lookup function to achieve this result. Any help will be appreciated! Thanks

回答1:

You can use wildcards with VLOOKUP. You'll have to rearrange your table so that the lookup column is the first column, but other than that it should work. I put:

abc 1
def 2
ghi 3
jkl 4

into a range named "table"

Then:

g*
g?i
?kl

into F2:F4

Now, in G2:G4, I put this formula:

=VLOOKUP(F2,table,2,FALSE)

and it correctly found 3 in cells G2 & G3, and 4 in cell G4.

The asterisk means zero or more characters; the question mark means exactly one character.

回答2:

This works as a wild card with text strings:

=vlookup("*"&REF&"*",lookup range, column,  false)

It turns a number into text to do a wild card on a number. Stars should go between the quotations but they are not showing up.

回答3:

You can do it this way:

=VLOOKUP(LOOKUP("value1",ColC:ColC,ColC:ColC), lookuprange, COLUMN(ColA) + 1, TRUE)

Of course you can simplify this with your domain knowledge.

This method allows you to use your lookup table as is.