To perform a reverse lookup with multiple criteria in Excel, use the INDEX and MATCH functions together, or the newer XLOOKUP function if you have Excel 2021 or Microsoft 365. For example, to find a product name based on a region and a salesperson, you can use: =INDEX(A2:A100, MATCH(1, (B2:B100="East")*(C2:C100="John"), 0)) entered as an array formula (Ctrl+Shift+Enter in older versions). This article explains the methods, how to choose between them, common pitfalls, and answers to frequent questions.
What Is a Reverse Lookup?
A normal VLOOKUP searches for a value in the leftmost column of a table and returns a value from a column to the right. A reverse lookup does the opposite: it finds a value in a column that is not the first, and returns a value from a column to its left. When you add multiple criteria, you are essentially asking Excel to find a row that matches several conditions at once, then return a value from that row.
For example, you might have a table with columns: Product ID (A), Region (B), Salesperson (C), and Units Sold (D). You want to find the Product ID for the row where Region is “West” and Salesperson is “Alice”. That is a reverse lookup with two criteria because the lookup columns (B and C) are to the right of the return column (A).
Method 1: INDEX and MATCH with Array Formula
The classic approach uses INDEX and MATCH with an array of TRUE/FALSE values. The MATCH function finds the position of the first row where all criteria are true, and INDEX returns the value from the return column at that position.
Steps:
- Assume your data is in A1:D100, with headers in row 1. The return column is A (Product ID), criteria columns are B (Region) and C (Salesperson).
- In a cell, enter:
=INDEX(A2:A100, MATCH(1, (B2:B100="West")*(C2:C100="Alice"), 0)) - If you are using Excel 2019 or earlier, press Ctrl+Shift+Enter to make it an array formula. In Excel 365 and 2021, a normal Enter works because dynamic arrays handle it.
The formula multiplies the two conditions. In Excel, TRUE*TRUE = 1, and any other combination gives 0. MATCH looks for the first 1, which corresponds to the first row where both conditions are met. The result is the Product ID from column A for that row.
If no row meets both criteria, MATCH returns an #N/A error. You can wrap the formula in IFERROR to display a friendly message: =IFERROR(INDEX(A2:A100, MATCH(1, (B2:B100="West")*(C2:C100="Alice"), 0)), "Not found").
Method 2: XLOOKUP with Boolean Logic (Excel 365/2021)
If you have Excel 2021 or Microsoft 365, XLOOKUP is simpler and does not require array entry. It can handle multiple criteria by concatenating lookup values and lookup arrays.
Syntax: =XLOOKUP(1, (B2:B100="West")*(C2:C100="Alice"), A2:A100)
Here, the lookup value is 1, and the lookup array is the product of the two conditions. XLOOKUP returns the corresponding value from A2:A100. It works without Ctrl+Shift+Enter because XLOOKUP is designed for dynamic arrays. You can also add an optional fourth argument for a default message: =XLOOKUP(1, (B2:B100="West")*(C2:C100="Alice"), A2:A100, "Not found").
Note: This method requires Excel 2021 or Microsoft 365. In older versions, XLOOKUP is not available.
Method 3: FILTER (for Multiple Matches)
If you expect more than one row to match your criteria, FILTER is the best choice. It returns all matching rows, not just the first. The syntax is: =FILTER(A2:A100, (B2:B100="West")*(C2:C100="Alice"), "No matches"). This spills all Product IDs that meet both conditions down the column. If you only want the first match, combine it with INDEX: =INDEX(FILTER(A2:A100, (B2:B100="West")*(C2:C100="Alice")), 1).
FILTER requires Excel 365 or 2021. It is the most flexible for multiple criteria because you can add as many conditions as needed by multiplying them together.
Choosing the Right Method
Your choice depends on your Excel version and whether you need a single match or all matches.
- Excel 2019 or older: Use INDEX/MATCH with array entry (Ctrl+Shift+Enter). It works reliably.
- Excel 2021 or Microsoft 365: Use XLOOKUP for a single match, FILTER for multiple matches, or INDEX/MATCH without array entry (since dynamic arrays handle it).
- If you might have duplicate matches: Use FILTER to see all, or use XLOOKUP/INDEX/MATCH to get the first one.
For large datasets, INDEX/MATCH is generally faster than VLOOKUP, but XLOOKUP is optimized and often the fastest. FILTER can be slower on very large data because it processes the entire array, but it is still acceptable for typical spreadsheet sizes.
Common Problems and Solutions
Here are frequent issues users face and how to fix them.
- #N/A error: No row matches all criteria. Double-check spelling, extra spaces, or data types (text vs. number). Use
TRIMto remove spaces:=INDEX(A2:A100, MATCH(1, (TRIM(B2:B100)="West")*(TRIM(C2:C100)="Alice"), 0)). - #VALUE! error: Often occurs when using array formula without Ctrl+Shift+Enter in older Excel. Re-enter with Ctrl+Shift+Enter, or switch to XLOOKUP if available.
- Wrong result: If you get a value but it is not the one you expect, check that your criteria ranges are the same size as the return range. Mismatched ranges cause misalignment.
- Case sensitivity: By default, MATCH and XLOOKUP are case-insensitive. If you need case-sensitive matching, use
EXACTinside the formula:=INDEX(A2:A100, MATCH(1, EXACT(B2:B100, "West")*EXACT(C2:C100, "Alice"), 0)). - Multiple criteria with OR logic: Instead of multiplying, add the conditions:
(B2:B100="West")+(B2:B100="East")gives 1 if either is true. Use that inside MATCH or XLOOKUP.
Frequently Asked Questions
Can I use VLOOKUP for reverse lookup with multiple criteria?
VLOOKUP cannot look to the left, so it is not suitable for reverse lookups. You could rearrange your data to put the lookup column first, but that is often impractical. INDEX/MATCH or XLOOKUP are the standard solutions.
How do I do a reverse lookup with three criteria?
Simply add another multiplication term. For example, with three criteria in columns B, C, and D, use: =INDEX(A2:A100, MATCH(1, (B2:B100="x")*(C2:C100="y")*(D2:D100="z"), 0)). The same logic extends to any number of criteria.
Why does my array formula not work without Ctrl+Shift+Enter?
In Excel 2019 and earlier, array formulas need to be entered with Ctrl+Shift+Enter to tell Excel to treat them as arrays. In Excel 365 and 2021, dynamic arrays make this unnecessary, so a normal Enter works. If you are using an older version, always press Ctrl+Shift+Enter after typing the formula.
What if I want to return a value from a column to the right of my criteria?
The same formulas work regardless of column order. Just change the range in INDEX or the return array in XLOOKUP to the column you want. For example, to return Units Sold (column D) instead of Product ID, use =INDEX(D2:D100, ...) or =XLOOKUP(1, ..., D2:D100).
Is there a way to do this without formulas?
Yes, you can use Excel’s built-in filtering: select your data, go to Data > Filter, then use the dropdown arrows on the criteria columns to select your values. The visible rows are your matches. To copy the results, you can use Go To Special > Visible Cells Only. This is a manual method, not dynamic.
How do I handle errors when no match is found?
Wrap your formula in IFERROR to display a custom message. For example: =IFERROR(INDEX(A2:A100, MATCH(1, (B2:B100="West")*(C2:C100="Alice"), 0)), "No match"). This works for all methods.
Final Tips for Reliable Lookups
Always use absolute references (like $B$2:$B$100) if you plan to copy the formula. Keep your criteria in separate cells so you can change them without editing the formula. For example, put “West” in E1 and “Alice” in E2, then use =INDEX(A2:A100, MATCH(1, (B2:B100=$E$1)*(C2:C100=$E$2), 0)). This makes the formula reusable and easier to maintain.
If you are working with a large dataset, consider using Excel Tables (Insert > Table). Table references like Table1[Region] automatically adjust when you add rows, reducing errors.
Remember that Excel formulas are not case-sensitive by default. If your data has mixed case and you need exact matches, use the EXACT function as shown earlier. Also be aware of leading or trailing spaces, which are a common cause of lookup failures. Use TRIM to clean data if necessary.
Related
- Excel Inventory Tracking Template Walkthrough
- Excel Jet Forecast? How to Build One
- Free Excel Invoice Template: XLS Download Guide
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Flash Fill Dates in Excel: How to Use ItTo flash fill dates in Excel for Microsoft 365 on Windows, enter one or two examples of the…
- Excel Day Number: Serial Dates, Day of Month and YearIf A2 contains a valid Excel date such as June 15, 2024, and you want its day of…
- How to Open Excel in Read-Only ModeIn Excel for Microsoft 365 on Windows, choose File > Open > Browse, select the workbook, then click…
- Can You Split a Cell in Google Sheets? Here’s HowYes, you can split the text in a Google Sheets cell into separate columns using Data > Split…
- Name Badge Template in Word: Create One Step by StepCreate a reusable name badge template in Word by setting up a page and sizing a table to…
- How to Delete Only Filtered Rows in Excel Without ErrorsTo delete only the rows currently shown by an Excel filter, select the filtered data cells, choose Visible…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.