Make Excel VLOOKUP and INDEX MATCH Distinguish Text Values
Question details
Make Excel lookup formulas distinguish literal text values, including identifiers with leading zeros and wildcard characters.

- 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.
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.
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.
Place the text to find in A2, the lookup identifiers in D2:D100, and the corresponding results in E2:E100.
Enter =VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"~","~~"),"*","~*"),"?","~?"),D2:E100,2,FALSE) in a result cell. FALSE selects an exact-match lookup.
Enter =INDEX(E2:E100,MATCH(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"~","~~"),"*","~*"),"?","~?"),D2:D100,0)) in a result cell. The 0 selects an exact match in MATCH.

Store identifiers with leading zeros as text
Use this method when values such as 15 and 015 must remain distinct identifiers.
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. Open the workbook: Open WPS Spreadsheets and select File > Open to choose your workbook.
- 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. Enter the lookup formula: In a result cell, enter =VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"~","~~"),"*","~*"),"?","~?"),D2:E100,2,FALSE).
- 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.

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.




