Function Problems

Excel INDEX MATCH Lookup Across Two Column Pairs

Maira MehtabMaira Mehtab Nov 12, 2026 869 views

Question details

Find an identifier in either of two lookup columns and return the associated value from the corresponding result column.

Excel INDEX MATCH Lookup Across Two Column Pairs
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the result cell

Click the cell where you want the returned asset or generator value to appear, such as B2 on your current worksheet.

2
Enter the dual-lookup formula

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.

3
Fill the formula down

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.

Use IFERROR with INDEX and MATCH to search both column pairs
How the lookup order works: The formula gives priority to a match in Borefield!B4:B30. A value in Borefield!J4:J30 is used only if the first lookup does not find the identifier.
WPS Spreadsheets formula help

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. 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. 2. Choose the output cell: Click the first result cell beside your lookup value, such as B2 when the identifier is in A2.
  3. 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. 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.
Use INDEX and MATCH for an exact lookup across two separate column pairs.Return values from the correct related column with IFERROR fallback logic.Open, edit, and save Microsoft Excel .xlsx and .xls workbooks.Use a familiar spreadsheet interface for formulas, fill handles, and cell references.
microsoft office alternative - wps office

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.