Excel INDEX and MATCH Lookup with Two Criteria
Question details
Return a category from another sheet by matching both an organization and an object.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains organization and object values, and a second worksheet contains the corresponding categories.
- Observed behavior
- The desired result is the category from the row where both lookup criteria match.
Check which columns contain the organization, object, and category on the source sheet. Make sure the lookup and return ranges cover the same rows.
Use INDEX and MATCH to Find a Category by Two Criteria
Use this formula when the category is on another sheet and both the organization and object must match.
The formula compares each source row against both criteria and returns the category from the first row where both match.
In Sheet1, place the organization in B2 and the object in C2. In Sheet2, use column A for organizations, column B for objects, and column C for categories.
Select Sheet1 cell A2 and enter =INDEX('Sheet2'!$C$2:$C$300,MATCH(1,INDEX(('Sheet2'!$A$2:$A$300=B2)*('Sheet2'!$B$2:$B$300=C2),0),0)).
Replace Sheet2 and the ranges with your actual sheet name and data rows, keeping all three ranges the same size. Drag the fill handle from A2 down to apply the formula to additional rows.
Look Up Categories by Two Criteria in WPS Spreadsheet
Open your workbook in WPS Spreadsheet and use INDEX and MATCH to return a category when both lookup values match.
- 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
- 2. Check the columns: Confirm that Sheet1 has the organization in B2 and object in C2, and that Sheet2 has organizations, objects, and categories in columns A, B, and C.
- 3. Enter the formula: Select Sheet1 cell A2 and enter =INDEX('Sheet2'!$C$2:$C$300,MATCH(1,INDEX(('Sheet2'!$A$2:$A$300=B2)*('Sheet2'!$B$2:$B$300=C2),0),0)). Adjust the sheet name and ranges to match your workbook.
- 4. Apply it to other rows: Drag the fill handle from A2 down to look up categories for the remaining rows, then use File > Save to save the workbook.
Frequently Asked Questions
What happens if more than one row matches both criteria?
MATCH returns the position of the first matching row, so INDEX returns that row's category.
Why does the formula return #N/A?
No row may match both criteria. Check Sheet1 cells B2 and C2 against the values in Sheet2 columns A and B, including any extra spaces, and verify that the ranges include the source row.
How do I use a different source sheet or larger range?
Replace each Sheet2 reference with the source sheet name and update all three ranges to cover the same rows. For example, change each ending row from 300 to 500.
Can I show a message instead of #N/A?
Wrap the formula in IFNA, for example: =IFNA(INDEX('Sheet2'!$C$2:$C$300,MATCH(1,INDEX(('Sheet2'!$A$2:$A$300=B2)*('Sheet2'!$B$2:$B$300=C2),0),0)),"Not found").
