Use IF and OR with an INDEX MATCH Formula in Excel
Question details
Use product code conditions to determine when an INDEX MATCH lookup should return a result.

- 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.
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.
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.
In Excel, click the cell where you want the lookup result to appear.
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.
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.
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. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
- 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. 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. Save the workbook: Check the result, then use File > Save or Save As to save the workbook.

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.




