Excel INDEX MATCH Lookup Across Two Column Pairs
Question details
Find an identifier in either of two lookup columns and return the associated value from the corresponding result column.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Asset or generator identifiers are stored in two separate lookup-and-return column pairs on the Borefield worksheet.
- Observed behavior
- The formula should search the first pair first, then search the second pair only when no exact match is found in the first pair.
Confirm that the lookup value is in cell A2 and that columns B and J contain the identifiers to search. Check that identifier values are stored consistently as text or numbers and do not contain unintended leading or trailing spaces.
Use IFERROR with INDEX and MATCH to search both column pairs
Use this formula when the same identifier may appear in either lookup column and each lookup column has its own return column.
The first INDEX and MATCH searches Borefield column B and returns the related value from column A. If no exact match is found, IFERROR runs the second INDEX and MATCH, which searches column J and returns the related value from column I.
Click the cell where you want the returned asset or generator value to appear, such as B2 on your current worksheet.
Type or paste =IFERROR(INDEX(Borefield!$A$4:$A$30,MATCH($A2,Borefield!$B$4:$B$30,0)),INDEX(Borefield!$I$4:$I$30,MATCH($A2,Borefield!$J$4:$J$30,0))) and press Enter.
Select the cell containing the formula and drag its fill handle down, or use Home > Fill > Down, to apply it to additional lookup values in column A.

Check unmatched identifiers and lookup range alignment
Use these checks when the formula returns an error or appears to miss an identifier that is visible in the source data.
Search Two Identifier Columns in WPS Spreadsheets
WPS Spreadsheets supports IFERROR, INDEX, and MATCH formulas, so you can search the first identifier column and automatically fall back to the second column when no match is found. Enter the formula once and fill it down for the rest of your records.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the Excel workbook containing the lookup sheet and the Borefield worksheet.
- 2. Choose the output cell: Click the first result cell beside your lookup value, such as B2 when the identifier is in A2.
- 3. Insert the nested lookup formula: Enter =IFERROR(INDEX(Borefield!$A$4:$A$30,MATCH($A2,Borefield!$B$4:$B$30,0)),INDEX(Borefield!$I$4:$I$30,MATCH($A2,Borefield!$J$4:$J$30,0))) and press Enter.
- 4. Apply the formula to remaining rows: Drag the fill handle from the formula cell down the result column, then save the workbook using Menu > Save or Save As.

Frequently Asked Questions
Why does the formula use IFERROR around two INDEX MATCH formulas?
IFERROR lets the first INDEX MATCH search Borefield!B4:B30 and return from Borefield!A4:A30. If that exact match returns an error because the identifier is absent, it runs the second lookup against Borefield!J4:J30 and returns the value from Borefield!I4:I30.
What happens if the identifier is not in either lookup column?
Both MATCH functions return #N/A, so the overall formula returns #N/A. To show a custom message instead, wrap the formula in another IFERROR, such as =IFERROR(your_formula,"Not found").
Which column is searched first when the same identifier appears in both columns?
The formula searches Borefield column B first. If the identifier exists in both B and J, it returns the value from the matching row in column A and does not use the column I result.
Do I need to press Ctrl+Shift+Enter for this formula?
No. This nested IFERROR, INDEX, and MATCH formula is entered normally by pressing Enter in current Excel and WPS Spreadsheets versions.
Why does MATCH return #N/A when the values look the same?
The values may have different data types or hidden spaces. Check whether one value is text and the other is numeric, and use TRIM on text values to remove extra spaces before matching.




