How to Use XLOOKUP for Exact Part Number Matches in Excel
Question details
The user needs to return accurate values for part numbers that share identical leading digits but differ in their alphanumeric endings.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Searching for specific inventory or part numbers from a large dataset where approximate matches return incorrect results.
- Observed behavior
- Searching for part numbers with similar starting digits requires an exact match method because traditional approximate lookups retrieve the wrong item.
Ensure you are using a version of Excel or WPS Spreadsheet that supports the XLOOKUP function (available in Microsoft 365, Excel 2021, and newer WPS Office versions). Verify that your dataset does not contain hidden trailing spaces, which can cause exact match formulas to fail.
Use an Exact-Match XLOOKUP Formula
Apply the XLOOKUP function with its default exact match behavior to accurately retrieve data for similar part numbers without needing to sort your dataset.
Unlike the older LOOKUP or VLOOKUP (without the FALSE argument), XLOOKUP defaults to an exact match. This makes it highly reliable for part numbers that share starting characters, preventing false positive matches.
It also allows you to specify a 'not found' message directly in the formula, eliminating the need for an additional IFERROR wrapper.
Click on the cell where you want the lookup result to appear.
Start typing the XLOOKUP formula using the syntax =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found]).
For example, type =XLOOKUP(B14, Data!A5:A404, Data!E5:E404, "Not Found") to search for the part number in cell B14 within the range A5:A404, and return the corresponding value from E5:E404.
Press Enter to execute the formula. If you need to perform subsequent calculations, append them directly to the formula, such as multiplying the result by cell AN5: =XLOOKUP(B14, Data!A5:A404, Data!E5:E404, "Not Found") * AN5.
Use VLOOKUP with EXACT Match Parameter
If you are using an older version of Excel that does not support XLOOKUP, use VLOOKUP with the FALSE parameter for exact matching.
Use XLOOKUP Seamlessly in WPS Spreadsheet
WPS Spreadsheet offers full support for modern Excel formulas, including XLOOKUP. You can easily manage complex inventory datasets and perform exact part number matches for free, with an interface that feels instantly familiar.
- 1. Open your workbook: Launch WPS Spreadsheet and open your Excel workbook containing the part numbers.
- 2. Select the target cell: Click on the cell where you want to output the part number details.
- 3. Enter the formula: Type =XLOOKUP( and let the formula tooltip guide you through selecting the lookup value, lookup array, and return array.
- 4. View results: Press Enter to instantly view your exact match results.
Frequently Asked Questions
Why does my XLOOKUP return 'Not Found' even though the part number exists?
This usually happens due to formatting differences or invisible trailing spaces in your dataset. Use the TRIM function on your lookup value or clean your data to ensure an exact textual match.
Can XLOOKUP perform a partial match if I only know the beginning of the part number?
Yes. You can use wildcard characters (like * or ?) in your lookup value and set the match_mode argument (the 5th parameter) to 2. For example: =XLOOKUP(B14&"*", Data!A5:A404, Data!E5:E404, "Not Found", 2).
What is the difference between XLOOKUP and VLOOKUP for part numbers?
XLOOKUP defaults to an exact match, can search from right to left, and doesn't break if you insert new columns. VLOOKUP requires the lookup column to be on the far left and defaults to an approximate match unless you specify FALSE.
How do I handle case-sensitive part numbers in XLOOKUP?
By default, XLOOKUP is not case-sensitive. To perform a case-sensitive exact match, you must combine XLOOKUP with the EXACT function, such as =XLOOKUP(TRUE, EXACT(B14, Data!A5:A404), Data!E5:E404).
