Formula Errors

Make Excel VLOOKUP and INDEX MATCH Distinguish Text Values

Steve KSteve K Nov 22, 2026 868 views

Question details

Make Excel lookup formulas distinguish literal text values, including identifiers with leading zeros and wildcard characters.

Make Excel VLOOKUP and INDEX MATCH Distinguish Text Values
Product
Microsoft Excel
Device & OS
not provided
Scenario
Using VLOOKUP or INDEX and MATCH to find an exact text identifier in a worksheet.
Observed behavior
Lookup results can be unexpected when a lookup value contains wildcard characters or when identifiers are stored as numbers instead of text.
Before you start

Check the lookup value and lookup column for asterisks, question marks, tildes, and leading zeros. Confirm whether the identifiers are stored as text or as numbers, since number formatting alone does not preserve leading zeros in the stored value.

Solution 1Recommended

Escape wildcard characters in exact-match formulas

Use this method when lookup values may contain literal asterisks, question marks, or tildes.

Excel treats * and ? as wildcards in exact-match text lookups. The formulas below escape tildes first, then asterisks and question marks, so those characters are matched literally.

These lookups are not case-sensitive. Escaping wildcards does not restore leading zeros that were lost when an identifier was stored as a number.

1
Set up the lookup range

Place the text to find in A2, the lookup identifiers in D2:D100, and the corresponding results in E2:E100.

2
Use VLOOKUP

Enter =VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"~","~~"),"*","~*"),"?","~?"),D2:E100,2,FALSE) in a result cell. FALSE selects an exact-match lookup.

3
Or use INDEX and MATCH

Enter =INDEX(E2:E100,MATCH(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"~","~~"),"*","~*"),"?","~?"),D2:D100,0)) in a result cell. The 0 selects an exact match in MATCH.

Escape wildcard characters in exact-match formulas
Check for duplicate identifiers: VLOOKUP and MATCH return the first matching entry. Check the lookup column if the same identifier appears more than once.
Exact text lookups in WPS

Find literal text values in WPS Spreadsheets

Use VLOOKUP or INDEX and MATCH in WPS Spreadsheets to find text identifiers, and escape wildcard characters when they must be treated literally.

  1. 1. Open the workbook: Open WPS Spreadsheets and select File > Open to choose your workbook.
  2. 2. Prepare text identifiers: Enter the lookup text in A2, identifiers in D2:D100, and results in E2:E100. Store identifiers with leading zeros as text.
  3. 3. Enter the lookup formula: In a result cell, enter =VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"~","~~"),"*","~*"),"?","~?"),D2:E100,2,FALSE).
  4. 4. Verify the result: Compare the returned value with the matching row in columns D and E. If an identifier is missing a leading zero, correct it using the original source.
Escape wildcard characters in exact text lookupsWork with Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and worksheet controlsEdit spreadsheets with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does an exact-match lookup treat an asterisk as a wildcard?

In a text lookup, VLOOKUP with FALSE and MATCH with 0 interpret * as a wildcard. Use ~* for a literal asterisk, or use SUBSTITUTE in the lookup formula to escape it automatically.

How can I keep 15 and 015 distinct in Excel?

Store both identifiers as text and enter the leading zero explicitly. If 015 was already stored as the number 15, restore it from the source or use a known identifier width.

Do I need to escape question marks and tildes too?

Yes. Use ~? for a literal question mark and ~~ for a literal tilde. When nesting SUBSTITUTE functions, escape existing tildes before escaping * and ?.

Are VLOOKUP and MATCH exact text lookups case-sensitive?

No. Standard exact-match VLOOKUP and MATCH formulas do not distinguish uppercase from lowercase letters.