Fix Excel XLOOKUP When a Formula Returns the Lookup Value
Question details
Find a matching value with XLOOKUP when the lookup value is generated by another formula.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A formula extracts a code from a cell, and XLOOKUP uses that code to search a lookup column.
- Observed behavior
- XLOOKUP returns #N/A because the extracted value may be incomplete or may not match the lookup column's spacing or format.
Identify the cell containing the source text and the columns containing the lookup codes and return values. Check that the code you need is always the last five characters of the source text.
Extract the Full Code Before Using XLOOKUP
Use this method when the source cell ends in a five-character code such as P0461.
In an empty cell, enter =RIGHT(A7) and compare the result with the code in your lookup column. For 'Hospitality - P0461', this returns only 1.
Replace the extraction formula with =RIGHT(TRIM(A7),5). For the example text, the result is P0461; TRIM removes ordinary leading and trailing spaces before extraction.
Enter =XLOOKUP(RIGHT(TRIM(A7),5),D2:D100,E2:E100,"Not found") in the result cell. Replace the example ranges with your lookup and return columns.
Compare the extracted code with the matching cell in D2:D100. Remove extra spaces and make sure both values are stored as text when the codes contain letters or leading zeros.

Clean Hidden Spaces in the Lookup Column
Use a helper column if the correct code still does not match because the lookup data contains extra or nonbreaking spaces.
Find Formula-Generated Codes in WPS Spreadsheets
Use RIGHT and TRIM to extract a code, then use INDEX and MATCH to return its corresponding value in WPS Spreadsheets.
- 1. Open the workbook: Open WPS Spreadsheets, select Open, and choose your Excel workbook.
- 2. Extract the code: In an empty cell, enter =RIGHT(TRIM(A7),5) to extract a five-character code from A7.
- 3. Look up the result: In the result cell, enter =INDEX(E2:E100,MATCH(RIGHT(TRIM(A7),5),D2:D100,0)). Replace the example ranges with your return and lookup columns.
- 4. Check the match: If the formula returns #N/A, compare the extracted code with the lookup column and remove extra spaces or inconsistent text formatting.

Frequently Asked Questions
Why does RIGHT(A7) return only one character?
When the character count is omitted, RIGHT returns one character. Use =RIGHT(A7,5) to return the last five characters.
Why does XLOOKUP still return #N/A after I extract the code?
Compare the extracted value with the lookup column for extra spaces, nonbreaking spaces, or different text and number formats. Use TRIM and, if needed, SUBSTITUTE with CHAR(160) to clean spaces.
Can I use this formula if my codes have different lengths?
A fixed count such as RIGHT(A7,5) will not reliably extract codes of different lengths. Use a formula based on the separator before the code instead.
What can I use if my Excel version does not support XLOOKUP?
Use INDEX and MATCH, for example =INDEX(E2:E100,MATCH(RIGHT(TRIM(A7),5),D2:D100,0)), with ranges adjusted to your worksheet.




