How do you use an asterisk in VLOOKUP? Understanding Wildcards for Flexible Data Matching

The asterisk (*) is a powerful tool when working with the VLOOKUP function in spreadsheet software like Microsoft Excel or Google Sheets. It acts as a wildcard character, allowing you to perform searches that aren't an exact match. This can be incredibly useful when you need to find information based on a partial match, or when you have variations in your data that might otherwise prevent VLOOKUP from finding what you're looking for.

What is a Wildcard in VLOOKUP?

In the context of VLOOKUP, wildcards are special characters that represent one or more unknown characters. The two primary wildcard characters you'll encounter are:

Asterisk (*): Represents any sequence of zero or more characters.
Question Mark (?): Represents any single character.

For this article, we'll focus on the asterisk (*), as it's the most common and versatile wildcard for general pattern matching.

When to Use an Asterisk in VLOOKUP

The asterisk is your go-to when you need to find a value that:

Starts with a specific text.
Ends with a specific text.
Contains a specific text anywhere within the cell.
Matches a pattern with unknown characters in between.

How to Use the Asterisk (*) in VLOOKUP

To use the asterisk in VLOOKUP, you'll typically incorporate it into your lookup_value argument. You'll often combine it with other text to create a search pattern.

Let's break down the common scenarios:

Scenario 1: Finding a Value That Starts With a Specific Text

Imagine you have a list of product IDs, and you want to find information about all products that start with "PROD-". You can use the asterisk at the end of your lookup value.

Formula Structure:

=VLOOKUP("PROD-*", A1:B10, 2, FALSE)

Explanation:

"PROD-*": This is your lookup_value. It tells VLOOKUP to look for any value in the first column of your table_array that begins with "PROD-" followed by any number of characters (represented by the asterisk).
A1:B10: This is your table_array, the range of cells where you want to search.
2: This is your col_index_num, indicating that you want to return the value from the second column of your table_array.
FALSE: This ensures an exact match for the pattern.

Scenario 2: Finding a Value That Ends With a Specific Text

If you need to find records that end with a particular suffix, such as " - US", you'll place the asterisk at the beginning of your lookup value.

Formula Structure:

=VLOOKUP("*- US", A1:B10, 2, FALSE)

Explanation:

"*- US": This tells VLOOKUP to find any value that ends with " - US", with any characters preceding it.

Scenario 3: Finding a Value That Contains Specific Text Anywhere

To find a value that contains a specific word or phrase anywhere within the cell, you'll surround your text with asterisks.

Formula Structure:

=VLOOKUP("*apple*", A1:B10, 2, FALSE)

Explanation:

"*apple*": This will find any value in the first column that has "apple" anywhere within it, such as "Granny Smith Apple", "Apple Juice", or "Pineapple".

Scenario 4: Using a Cell Reference with an Asterisk

Often, you won't hardcode the lookup value. Instead, you'll have a cell containing the text you want to search for. In this case, you'll use the concatenation operator (&) to combine the asterisk with the cell reference.

Let's say cell D1 contains the text "PROD-". To find values starting with "PROD-", you would use:

Formula Structure:

=VLOOKUP(D1&"*", A1:B10, 2, FALSE)

If cell D1 contains "apple", and you want to find any value containing "apple", you would use:

Formula Structure:

=VLOOKUP("*"&D1&"*", A1:B10, 2, FALSE)

Explanation:

D1&"*": This concatenates the content of cell D1 with an asterisk. If D1 has "PROD-", the formula effectively becomes "PROD-*".
"*"&D1&"*": This concatenates an asterisk, the content of cell D1, and another asterisk. If D1 has "apple", the formula effectively becomes "*apple*".

Important Considerations When Using Asterisks

1. `match_type` Argument: For wildcard searches (using asterisks or question marks), you MUST set the match_type argument to FALSE (or 0). This tells VLOOKUP to perform an *exact match* for the pattern you've provided. If you set it to TRUE (or 1), VLOOKUP will look for the closest match in an *unsorted* range, which is not what you want for wildcard functionality and can lead to incorrect results.

2. Exact Match Required for Wildcards: Even though you're using wildcards to find partial matches within the data, the lookup_value itself, when combined with wildcards, is treated as the *exact pattern* VLOOKUP needs to find. This is why FALSE is crucial.

3. Text-Based Searches: Wildcards in VLOOKUP work with text strings. If your lookup column contains numbers, you'll need to ensure they are formatted as text or convert them to text for the wildcard to function correctly. For example, if your IDs are stored as numbers, you might need to wrap your lookup_value or the lookup column in the TEXT() function.

4. The Tilde (~) for Literal Asterisks: What if you actually want to search for a literal asterisk within your data? You would use the tilde (~) character before the asterisk. For example, to find a cell that contains "Item * 10", you would use "Item ~* 10" as your lookup_value.

5. Performance: While powerful, using wildcards in VLOOKUP can sometimes be slower than exact matches, especially with very large datasets. If performance becomes an issue, consider if there are ways to refine your data or use alternative functions.

Example Scenario

Let's say you have the following data in cells A1:B5:

| Column A (Product Name) | Column B (Price) |

|-------------------------|-----------------|

| Apple Juice | $3.50 |

| Fuji Apple | $1.20 |

| Pineapple | $2.00 |

| Organic Apple | $1.80 |

Goal 1: Find the price of any product containing "Apple".

In cell D1, you type "Apple". In cell E1, you enter the formula:

=VLOOKUP("*"&D1&"*", A1:B5, 2, FALSE)

This formula will return $3.50 because it finds "Apple Juice" first, which matches the pattern.

Goal 2: Find the price of products that start with "Fuji".

In cell D2, you type "Fuji". In cell E2, you enter the formula:

=VLOOKUP(D2&"*", A1:B5, 2, FALSE)

This formula will return $1.20.

FAQ Section

How do I search for values that contain a specific word using VLOOKUP?

To search for values that contain a specific word, you need to surround the word with asterisks in your lookup_value. For example, if you want to find any record containing the word "Report", you would use =VLOOKUP("*Report*", A1:B10, 2, FALSE). Remember to set the last argument to FALSE for an exact pattern match.

Why do I need to use FALSE with asterisks in VLOOKUP?

When you use wildcards like the asterisk, you are instructing VLOOKUP to look for a specific *pattern*. The FALSE argument tells VLOOKUP to perform an exact match for that pattern. If you were to use TRUE, VLOOKUP would attempt to find the closest match in an *unsorted* range, which is not how wildcards are intended to be used and would likely produce incorrect results.

What happens if my data contains an actual asterisk character I need to search for?

If you need to search for a literal asterisk character within your data, you must escape it using a tilde (~). So, if you were looking for a cell that contained "Item * Special", your lookup_value would be "Item ~* Special". This tells VLOOKUP to treat the asterisk as a regular character, not a wildcard.

Can I use asterisks with numbers in VLOOKUP?

Wildcards in VLOOKUP are primarily designed for text strings. If your lookup column contains numbers that you want to treat as text for wildcard matching (e.g., phone numbers with leading zeros, product codes), you'll need to ensure those numbers are formatted as text. You can achieve this by pre-formatting the column as text or by using the TEXT() function to convert your lookup_value to text if it's a number.

Does using asterisks in VLOOKUP affect the order of my data?

No, using asterisks as wildcards does not require your data to be sorted. However, it's crucial to set the `match_type` argument to `FALSE` to ensure an exact match for the pattern. If you were to use `TRUE`, then your lookup column *would* need to be sorted for VLOOKUP to function correctly, but this is generally not recommended when using wildcards.