VLOOKUP Wildcard Match: How-To Guide

This guide shows you how to use Excel’s VLOOKUP function with wildcard characters to find partial matches in your data. Instead of requiring an exact match, you can search for values that start with, end with, or contain a specific text pattern. This is useful when your lookup value isn’t entered exactly as it appears in the table, such as when you have extra spaces, prefixes, or inconsistent naming.

What You’ll Need

• Microsoft Excel (2010 or later, including Microsoft 365) or Google Sheets
• A table with at least two columns: one column containing the text you want to search (the lookup column), and another column with the value you want to return.
• The lookup column must be the leftmost column in the table range you specify.

Step-by-Step

1. Identify your lookup value and table array. For example, you have a product list in columns A (Product Code) and B (Price). You want to find the price for a code that starts with “ABC”. Your lookup value is “ABC*”, your table array is A:B, and the column index is 2.
2. Understand the wildcard characters. Excel supports two wildcards: the asterisk (*) matches any sequence of characters (including zero characters), and the question mark (?) matches any single character. A tilde (~) before a wildcard treats it as a literal character (e.g., “~?” looks for a question mark).
3. Construct the VLOOKUP formula. The syntax is: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). For a wildcard match, set the last argument to FALSE (or 0). This tells Excel to find the first value in the lookup column that matches the pattern.
4. Enter the formula with a wildcard pattern. For example: =VLOOKUP("ABC*", A:B, 2, FALSE). This searches column A for the first entry that starts with “ABC” and returns the corresponding value from column B.
5. Use a cell reference for the lookup value. Instead of typing the text directly, reference a cell. For instance, if cell D1 contains “ABC”, use =VLOOKUP(D1&"*", A:B, 2, FALSE). The ampersand (&) concatenates the cell value with the wildcard.
6. Handle partial matches in the middle. To find a value that contains a certain text anywhere, put asterisks on both sides: =VLOOKUP("*"&D1&"*", A:B, 2, FALSE). This matches any cell in column A that has the text from D1 anywhere within it.
7. Match a single character. Use a question mark to match exactly one character. For example, =VLOOKUP("A?C", A:B, 2, FALSE) matches “ABC”, “A1C”, “A-C”, etc., but not “AC” or “ABBC”.
8. Copy the formula down. If you have multiple lookup values, drag the fill handle down to apply the formula to other cells. Ensure your table array is absolute (e.g., $A$1:$B$100) if you don’t want it to shift.

Common Mistakes

• Forgetting the range_lookup argument. If you omit the fourth argument, VLOOKUP defaults to TRUE (approximate match), which requires the lookup column to be sorted and won’t handle wildcards correctly. Always use FALSE for wildcard searches.
• Using wildcards with TRUE. Even if you remember the fourth argument, using TRUE with wildcards will not work as expected; it may return an approximate match or an error.
• Putting wildcards in the wrong place. A wildcard only applies to the lookup_value, not to the table array. You cannot put an asterisk in the table array to search for a pattern.
• Expecting multiple matches. VLOOKUP returns only the first match it finds. If your data has multiple entries that match the pattern, you will only get the first one. Use INDEX/MATCH or FILTER if you need all matches.
• Not using absolute references when copying. If you copy the formula down and the table array is relative, it will shift and may not cover the correct data range.

Tips

• Use wildcards with caution on large datasets. Wildcard searches can be slower than exact matches, especially on thousands of rows. If performance is an issue, consider adding a helper column that extracts the part you need to match.
• Combine with TRIM and CLEAN. If your data has extra spaces or non-printable characters, wrap the lookup value or the lookup column with TRIM and CLEAN to avoid false negatives.
• Use case-insensitive matching. VLOOKUP is not case-sensitive by default. If you need case-sensitive wildcard matching, you’ll need a more advanced formula using EXACT and array formulas.
• Test with a simple example first. Before applying to your full dataset, test the formula on a small sample to ensure the wildcard pattern behaves as expected.
• Consider using XLOOKUP. In Excel 365 and 2021, XLOOKUP supports wildcards with a dedicated match_mode argument (2 for wildcards) and can return multiple matches. It’s often simpler and more powerful.

Troubleshooting

• #N/A error. This means no match was found. Check that your wildcard pattern is correct and that the lookup column actually contains a value that matches. Also ensure you used FALSE for the range_lookup.
• #VALUE! error. This usually occurs if the col_index_num is less than 1 or greater than the number of columns in the table array. Verify your column index.
• #REF! error. This means the col_index_num refers to a column outside the table array. For example, if your table array is A:B and you ask for column 3, you’ll get #REF!.
• Returns the wrong value. If you get a value but it’s not the one you expected, check that the lookup column is the leftmost column of the table array. VLOOKUP always searches the first column of the array. Also, if you used TRUE by mistake, it may return an approximate match.
• Wildcard not working. If the wildcard seems to be ignored, check that the cell format is text, not number. Also, if you’re using a tilde (~) in your pattern, remember to double it if you want a literal tilde.
• Formula works in one cell but not when copied. This is almost always a relative reference issue. Use absolute references (e.g., $A$1:$B$100) for the table array.

Frequently Asked Questions

Can I use VLOOKUP wildcards to match numbers?

Yes, but the numbers are treated as text during the match. If your lookup column contains numbers stored as text, it works. If they are actual numbers, you may need to convert them to text first or use a different approach.

How do I find the last match instead of the first?

VLOOKUP always returns the first match. To get the last match, you can use an array formula like =LOOKUP(2,1/ISNUMBER(SEARCH("pattern",A:A)),B:B) or use XLOOKUP with the search mode set to “search from last”.

Does wildcard VLOOKUP work in Google Sheets?

Yes, Google Sheets supports wildcards in VLOOKUP with the same syntax. The asterisk and question mark work the same way.

Can I use wildcards with the range_lookup set to TRUE?

Technically, Excel will allow it, but it won’t perform a proper wildcard match. It may return an approximate match based on the sorted order, which is rarely what you want. Always use FALSE.

How do I match a literal asterisk or question mark?

Precede the character with a tilde (~). For example, =VLOOKUP("*~?*", A:B, 2, FALSE) finds a cell that contains a question mark anywhere.

What if my lookup value is longer than 255 characters?

VLOOKUP with wildcards will not work reliably with very long strings. Consider using INDEX/MATCH with SEARCH, or split the text into smaller parts.

Related

• Recover Deleted Safari History on Mac: Full Guide
• Hulu Buried Smartest Sci-Fi Thrillers in Lab?
• Restore PostgreSQL Database: Full Guide