Function Problems

Return Matching IDs with XLOOKUP or INDEX MATCH in Excel

Chanuka GeekiyanageChanuka Geekiyanage Nov 18, 2026 868 views

Question details

Return the ID associated with each value in column I by matching it against values in column R.

Return Matching IDs with XLOOKUP or INDEX MATCH in Excel
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.
Before you start

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.

Solution 1Recommended

Use XLOOKUP to Return Matching IDs

Use this formula if your Excel version supports XLOOKUP and you want a message for missing values.

1
Enter the formula

Select cell J3 and enter =XLOOKUP(I3,$R$3:$R$61,$S$3:$S$61,"Not found").

2
Fill down

Drag the fill handle from J3 down to J450, or select J3:J450 and use Home > Fill > Down.

Use XLOOKUP to Return Matching IDs
Keep the lookup ranges fixed: The dollar signs keep the lookup and return ranges fixed as the formula is filled down. Adjust the ranges if your lookup table extends beyond row 61.
Work with matching IDs in WPS

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. 1. Open your workbook: Open WPS Spreadsheets and use Open to select your workbook.
  2. 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. 3. Fill the formula down: Drag the fill handle from J3 to J450 to return an ID for each source row.
  4. 4. Save the workbook: Use File > Save As to save the workbook in your preferred spreadsheet format.
Match column I values to IDs in column SOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceWork with spreadsheet formulas for free
microsoft office alternative - wps office

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.