Function Problems

Use IF and OR with an INDEX MATCH Formula in Excel

Jason MillerJason Miller Nov 14, 2026 870 views

Question details

Use product code conditions to determine when an INDEX MATCH lookup should return a result.

Use IF and OR with an INDEX MATCH Formula in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A product code is checked for specific characters before a row and column lookup is performed.
Observed behavior
When any specified condition is met, the formula should return the matching table value; otherwise, it should return a blank.
Before you start

Check that B37 contains the product code, B42 contains the row lookup value, and B40 contains the column lookup value. Confirm that I13:I29 and J12:O12 contain the corresponding lookup labels and that J13:O29 contains the results.

Solution 1Recommended

Combine IF and OR with INDEX MATCH

Use OR when several product code conditions should all return a result from the same lookup table.

The formula checks whether the code ends in A or E, or has 0 as its sixth character. If any condition is true, INDEX returns the value at the row and column found by MATCH.

If different conditions require different lookup tables, use nested IF functions and place the appropriate INDEX MATCH formula in each branch instead.

1
Select the result cell

In Excel, click the cell where you want the lookup result to appear.

2
Enter the formula

Type or paste =IF(OR(RIGHT(B37,1)="A",RIGHT(B37,1)="E",MID(B37,6,1)="0"),INDEX(J13:O29,MATCH(B42,I13:I29,0),MATCH(B40,J12:O12,0)),"") into the formula bar, then press Enter.

3
Check the result

Compare the result cell with the product code in B37 and the lookup values in B42 and B40. Adjust the cell references in the formula bar if your data is in different locations.

Exact matches are required: Both MATCH functions use 0 for exact matching. If a lookup value is not found, Excel returns #N/A; if none of the product code conditions is met, the formula returns a blank.
Work with lookup formulas

Use the Same Conditional Lookup in WPS Spreadsheets

WPS Spreadsheets supports IF, OR, INDEX, and MATCH, so you can enter the conditional lookup formula and work with your Excel workbook.

  1. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
  2. 2. Check the source cells: Locate the product code in B37, the lookup values in B42 and B40, and the table in J13:O29. Update the references if your sheet uses different cells.
  3. 3. Enter the lookup formula: Select the result cell and enter =IF(OR(RIGHT(B37,1)="A",RIGHT(B37,1)="E",MID(B37,6,1)="0"),INDEX(J13:O29,MATCH(B42,I13:I29,0),MATCH(B40,J12:O12,0)),"") in the formula bar, then press Enter.
  4. 4. Save the workbook: Check the result, then use File > Save or Save As to save the workbook.
Combine product code conditions with a two-way lookupOpen and edit Microsoft Excel XLSX filesUse familiar spreadsheet formulas and a convenient formula barUse WPS Office for free
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula return a blank?

The IF formula returns an empty string when B37 does not end in A or E and its sixth character is not 0. Check the product code in B37 and adjust the OR conditions if needed.

Why am I getting a #N/A error?

One of the MATCH functions could not find an exact match. Check that B42 appears in I13:I29 and B40 appears in J12:O12, and verify that the lookup ranges are correct.

How do I use a different lookup table for each condition?

Replace the single OR test with nested IF functions. Put the INDEX MATCH formula for the first table in the first IF's true branch, then put another IF and its lookup formula in the false branch.

Can I use this formula in WPS Spreadsheets?

Yes. Open your workbook in WPS Spreadsheets, select a result cell, and enter the same IF, OR, INDEX, and MATCH formula in the formula bar. Adjust the references to match your sheet.