Function Problems

How to Fix VLOOKUP Not Distinguishing D from D* in Excel

Aamir Naveed Akram Sep 25, 2026 870 views

Question details

The user needs to fix an issue where the VLOOKUP function evaluates text strings with asterisks incorrectly, failing to distinguish between 'D' and 'D*'.

Product: Excel
Device & OS: not provided
Scenario: Retrieving data points using VLOOKUP based on text values that include asterisk characters.
Observed behavior: VLOOKUP incorrectly returns the same or incorrect result for 'D' and 'D*', likely treating the asterisk as a wildcard rather than a literal character.

Before you start

Verify if the asterisk (*) in your dataset is intended to be a literal character or a wildcard, and ensure there are no trailing hidden spaces in your lookup values.

Solution 1Recommended

Use Exact Match VLOOKUP and Escape Wildcards

Adjust your VLOOKUP formula to require an exact match and escape the asterisk character using a tilde.

By default, VLOOKUP treats the asterisk (*) as a wildcard representing any number of characters. If you need to search for a literal asterisk, you must escape it so the function reads it as standard text.

Set Exact Match Mode

Ensure the fourth argument of your VLOOKUP formula is set to FALSE or 0 to force an exact match. For example: =VLOOKUP(D2, $A$2:$B$5, 2, FALSE).

Escape the Asterisk

To reference a literal asterisk, insert a tilde (~) before it. You can automate this by wrapping the lookup value in the SUBSTITUTE function: =VLOOKUP(SUBSTITUTE(D2, "*", "~*"), $A$2:$B$5, 2, FALSE).

Use Exact Match VLOOKUP and Escape Wildcards

Wildcard Escaping: The tilde (~) is an escape character that tells the spreadsheet to treat the subsequent wildcard (* or ?) as a regular text character.

Solution 2

Switch to the XLOOKUP Function

Replace VLOOKUP with XLOOKUP, which treats asterisks as literal characters by default.

Solution 3

Clean Source Data Formatting

Remove invisible spaces or inconsistent formatting that might cause lookup failures.

Advanced Spreadsheet Tools

Handle Complex Lookups Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced lookup functions like VLOOKUP and XLOOKUP, ensuring accurate data retrieval even with tricky wildcard characters.

1. Open Your Data File: Launch WPS Spreadsheet and open your existing workbook containing the lookup tables.
2. Select Result Cell: Click the cell where you want the calculated lookup value to display.
3. Enter the Lookup Formula: Type your preferred exact-match formula, such as =XLOOKUP(D2, $A$2:$A$5, $B$2:$B$5), and press Enter.
4. Fill Down the Column: Click and drag the fill handle at the bottom right corner of the cell to apply the formula to the rest of the column.

100% compatible with Microsoft Excel formulas and formatsModern XLOOKUP function built-in for easier searchesFree, lightweight, and user-friendly interfacePowerful data cleaning and formatting tools

microsoft office alternative - wps office

Frequently Asked Questions

Why does VLOOKUP treat an asterisk as a wildcard by default?

Spreadsheet software uses the asterisk (*) to represent any sequence of characters in search and lookup functions, which allows users to perform flexible partial matches easily.

How do I force VLOOKUP to search for a literal asterisk?

Place a tilde (~) immediately before the asterisk (~*) in your lookup value. The tilde acts as an escape character, overriding the default wildcard behavior.

Does XLOOKUP have the same wildcard issue?

No, XLOOKUP searches for literal characters by default. To use wildcards in XLOOKUP, you must explicitly set the match_mode argument to 2.

Why does VLOOKUP recognize A* but fail on D*?

This can happen if the wildcard match for 'A*' happens to return the correct row by coincidence, whereas 'D*' matches the first instance of any text starting with 'D', returning an incorrect row. Escaping the wildcard ensures consistent exact matches.

Excel & Data Problems