Return Matching IDs with XLOOKUP or INDEX MATCH in Excel
Question details
Return the ID associated with each value in column I by matching it against values in column R.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The lookup values are in column I, the unique matching values are in R3:R61, and their corresponding IDs are in S3:S61.
- Observed behavior
- Each row should display its matching ID in column J, with a clear result when no match is found.
Check that the lookup values are in R3:R61 and their corresponding IDs are in S3:S61. Make sure the values in column I and column R use consistent data types, such as text or numbers.
Use XLOOKUP to Return Matching IDs
Use this formula if your Excel version supports XLOOKUP and you want a message for missing values.
Select cell J3 and enter =XLOOKUP(I3,$R$3:$R$61,$S$3:$S$61,"Not found").
Drag the fill handle from J3 down to J450, or select J3:J450 and use Home > Fill > Down.

Use INDEX and MATCH in Older Excel Versions
Use INDEX and MATCH if XLOOKUP is unavailable in your version of Excel.
Look Up Matching IDs in WPS Spreadsheets
Use INDEX and MATCH in WPS Spreadsheets to return IDs from a lookup table and fill the formula down your data.
- 1. Open your workbook: Open WPS Spreadsheets and use Open to select your workbook.
- 2. Enter the lookup formula: Select J3 and enter =IFERROR(INDEX($S$3:$S$61,MATCH(I3,$R$3:$R$61,0)),"Not found").
- 3. Fill the formula down: Drag the fill handle from J3 to J450 to return an ID for each source row.
- 4. Save the workbook: Use File > Save As to save the workbook in your preferred spreadsheet format.

Frequently Asked Questions
What if my Excel version does not support XLOOKUP?
In J3, use =IFERROR(INDEX($S$3:$S$61,MATCH(I3,$R$3:$R$61,0)),"Not found") and fill it down.
Can I use VLOOKUP instead?
Yes. Enter =VLOOKUP(I3,$R$3:$S$61,2,FALSE) in J3 and fill down. FALSE requests an exact match; wrap the formula in IFERROR to display a custom message for missing values.
Why does the formula say “Not found” when a value appears in column R?
Check the cells in columns I and R for extra spaces or different data types, such as a number stored as text in one column and as a number in the other.
What happens if column R contains duplicate values?
XLOOKUP and MATCH return the first matching value by default. Check column R for duplicates if each value should have a unique ID.




