VLOOKUP with Wildcard in Excel

VLOOKUP with Wildcard in Excel

The VLOOKUP function in Excel is a powerful tool used to search for a value in the first column of a range and return a corresponding value in another column.  However, what if you are unsure of the precise lookup value? That’s where wildcards come in!

What Are Wildcards?

In text search, wildcards let you make partial matches. VLOOKUP utilizes two wildcards in Excel:

* (an asterisk): any number of characters that match

?  (question mark) — corresponds to any individual character

VLOOKUP with Wildcard Syntax:

=VLOOKUP("*"&E3&"*", B3:C6, 2, 0)

Let's break this down in detail:

• E3 This cell contains the color name you want to search for.
• In the image, E3 = "Green".

"*"&E3&"*"

• This creates a wildcard pattern: *Green*.
• The asterisks * are wildcards that mean "any text before or after".
• So it searches for any text that contains the word “Green”.

VLOOKUP("*"&E3&"*", B3:C6, 2, 0)

Let’s break this down:

• =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value = "*Green*"

• You're telling Excel to look for any text that contains “Green”.

table_array = B3:C6

• This is the range where you're searching:
• Column B has color descriptions.
• Column C has corresponding index values.

col_index_num = 2

• It returns the value from the 2nd column in the selected range, which is Column C (Index values).

range_lookup = 0

• 0 means Exact match (wildcard supported for text search).
• It ensures that VLOOKUP checks for the exact match using the wildcard.

How It Works:

Your formula searches the range B3:B6 for any text containing the word "Green".

From the data:

• B6 = "Agriculuter color is Green" → this matches "Green"
• Corresponding value in C6 = 13

If you enjoyed learning about VLOOKUP WITH WILD CARD, there's so much more waiting for you! Dive deeper into the world of Excel with my eBook “10 Ultimate Excel Lookup Formulas” — your complete guide to mastering powerful lookup functions like VLOOKUP, HLOOKUP, INDEX + MATCH, XLOOKUP, LOOKUP, FILTER, CHOOSE and more.

Whether you're a beginner or an advanced user, this book is packed with real-world examples, practical tips, and easy-to-follow explanations to boost your productivity and confidence in Excel.