Function Problems

Fix Excel XLOOKUP When a Formula Returns the Lookup Value

Adam DavisAdam Davis Nov 18, 2026 868 views

Question details

Find a matching value with XLOOKUP when the lookup value is generated by another formula.

Fix Excel XLOOKUP When a Formula Returns the Lookup Value
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.
Before you start

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.

Solution 1Recommended

Extract the Full Code Before Using XLOOKUP

Use this method when the source cell ends in a five-character code such as P0461.

1
Inspect the extracted value

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.

2
Extract five characters

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.

3
Update XLOOKUP

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.

4
Check the lookup column

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.

Extract the Full Code Before Using XLOOKUP
Codes of different lengths: RIGHT(...,5) works only when the desired code has five characters. Adjust the character count or use a formula based on the separator if code lengths vary.
Work with lookup formulas in WPS Spreadsheets

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. 1. Open the workbook: Open WPS Spreadsheets, select Open, and choose your Excel workbook.
  2. 2. Extract the code: In an empty cell, enter =RIGHT(TRIM(A7),5) to extract a five-character code from A7.
  3. 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. 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.
Extract and look up codes generated from cell textOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and a familiar interfaceWork with spreadsheets for free
microsoft office alternative - wps office

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.