Yes. In Google Sheets, INDEX and MATCH can find a value using multiple criteria by multiplying the criteria tests inside MATCH. With regions in A2:A100, products in B2:B100, results in C2:C100, and the requested region and product in E2 and F2, use =INDEX(C2:C100,MATCH(1,(A2:A100=E2)*(B2:B100=F2),0)). It returns the result from the first row that meets both conditions.
How INDEX and MATCH work together
INDEX returns a value at a specified position in a range. MATCH searches a range or calculated array and returns the position of a matching item. Used together, MATCH supplies INDEX with the row position of the record you want. When the criteria are in separate columns, the comparison tests can be combined so MATCH looks for a row where every test is true.
In the formula above, A2:A100=E2 checks each region against the requested region. The second test, B2:B100=F2, checks each product. Each comparison produces an array of TRUE and FALSE values. Multiplying the arrays treats TRUE as 1 and FALSE as 0: only a row where both tests are true produces 1. MATCH searches for that 1, and INDEX returns the corresponding cell from column C.
The final zero in MATCH requests an exact match. This matters because the criteria should identify the requested values, not the closest value in a sorted list. Google Sheets evaluates the array comparisons in this formula directly; you do not need to enter it as a special array formula or press Ctrl+Shift+Enter.
Set up a clear example
Suppose a small order table uses column A for region, column B for product, and column C for units sold. Cell E2 contains West and F2 contains Notebook. If the first row meeting both conditions is row 8 and C8 contains 17, the formula returns 17. A row with West and a different product does not qualify, and neither does a Notebook order from another region.
To adapt the formula to your sheet, replace the three data ranges with your result and criteria columns, then replace E2 and F2 with the cells holding your lookup values. Keep all ranges the same height: for example, A2:A100, B2:B100, and C2:C100 align row by row. If one range starts on row 2 and another starts on row 3, the comparisons no longer describe the same records.
For three criteria, add another comparison to the multiplication. With region in A2:A100, product in B2:B100, year in D2:D100, results in C2:C100, and requested values in E2, F2, and G2, the formula is =INDEX(C2:C100,MATCH(1,(A2:A100=E2)*(B2:B100=F2)*(D2:D100=G2),0)). Each extra comparison adds another condition that must be true.
Return a helpful message when nothing matches
When MATCH cannot find a qualifying row, it returns an #N/A error. That is useful while building a formula because it signals that no row met the conditions, but a finished report may need a clearer result. With the same ranges and criteria cells as the order example, wrap the lookup in IFNA:
=IFNA(INDEX(C2:C100,MATCH(1,(A2:A100=E2)*(B2:B100=F2),0)),"No match")
This displays No match when the lookup produces #N/A. IFNA only handles that specific error, so it does not hide every possible problem in the formula. If you would rather show a blank, replace the text argument with an empty string: =IFNA(INDEX(C2:C100,MATCH(1,(A2:A100=E2)*(B2:B100=F2),0)),""). A blank can make a report look cleaner, but it can also make it less obvious that the criteria found nothing.
Return every matching row instead of one
INDEX with MATCH returns one result: the first qualifying match in the search range. It is not a way to list every matching record. If you want all matching results to spill into cells below the formula, Google Sheets’ FILTER function is often a better fit. With the same order-table layout and criteria cells, use:
=FILTER(C2:C100,A2:A100=E2,B2:B100=F2)
This returns the values from column C for every row where the region and product conditions are both true. FILTER can return multiple rows, while INDEX MATCH is useful when you specifically need one value, such as a price, status, or matching record. If FILTER finds no qualifying rows, it returns #N/A; you can handle that with IFNA if you want a message or blank instead.
To return whole matching records rather than only the values in column C, use the table range as FILTER’s first argument. With the order data in A2:C100 and criteria in E2 and F2, use =FILTER(A2:C100,A2:A100=E2,B2:B100=F2). The output includes the region, product, and units for each match. Make sure the cells where results will expand are empty, or the array output may be blocked.
AND criteria versus OR criteria
Multiplying comparisons means AND: a row must meet every listed condition. This is the usual meaning of “multiple criteria” in a lookup, such as a customer name and invoice number together. FILTER uses separate condition arguments for the same AND behavior, as in the example above.
OR means a row can meet either condition. With regions in A2:A100, products in B2:B100, results in C2:C100, and criteria in E2 and F2, an OR-style FILTER formula is =FILTER(C2:C100,((A2:A100=E2)+(B2:B100=F2))>0). The addition combines the TRUE/FALSE tests, and the greater-than-zero check keeps rows where at least one test is true. Be deliberate about AND versus OR: switching between them changes which records qualify, not merely how the formula is written.
Common problems and how to diagnose them
- #N/A: No row met all the criteria, or a criterion differs from the source value. Check spelling, extra spaces, and whether the requested values are in the intended cells.
- Ranges with different sizes: The result range and each criteria range should cover the same rows. Use matching start and end rows so each test refers to the same record.
- Numbers stored differently: A number stored as text may not compare as expected with a numeric criterion. Check the underlying cell values and make the data types consistent rather than changing the lookup blindly.
- Unexpected first result: INDEX MATCH returns the first qualifying row from top to bottom. If duplicate combinations exist, check for duplicates or use FILTER to display all matches.
- Blank criteria cells: A blank lookup cell can match blank cells in a criteria column. If blank criteria should mean “do not search,” handle that separately rather than assuming the formula will ignore it.
- Hidden whitespace: Values that look identical may contain leading or trailing spaces. Clean the source data or use a consistent cleanup process; applying cleanup functions to large ranges inside a lookup can make formulas harder to maintain.
When to use a helper column or another approach
The multiplication formula is usually straightforward for a few criteria. For a very wide dataset or a formula that many people need to audit, a helper column can make the matching logic easier to inspect. For example, with region in A2, product in B2, and the helper key in D2, enter =A2&"|"&B2 in D2 and fill it down. With lookup values in E2 and F2 and the helper keys in D2:D100, an INDEX MATCH lookup can then use =INDEX(C2:C100,MATCH(E2&"|"&F2,D2:D100,0)).
A delimiter such as the vertical bar helps keep combined values distinct, but it is not foolproof if that character can appear in the source data. For important work, choose a delimiter that cannot occur in the values or stick with separate comparison tests. A helper column also adds a field to maintain, so it is most useful when clarity, reuse, or troubleshooting matters more than keeping the table compact.
For a lookup that needs one result, INDEX MATCH is a good choice and works with exact comparisons. For a list of all qualifying records, FILTER is more direct. Neither formula automatically resolves duplicate records according to a business rule; decide whether the first match is acceptable, whether duplicates should be reviewed, or whether another criterion is needed to identify a unique record.
Frequently Asked Questions
Can INDEX MATCH use three or more criteria in Google Sheets?
Yes. Add another comparison in parentheses and multiply it into the MATCH lookup array. Every comparison must be true for a row to qualify, and the ranges must align.
Does the formula return the first match or all matches?
INDEX MATCH returns the first matching result in the range. Use FILTER when you need to see every matching row or value.
Do I need Ctrl+Shift+Enter for multiple criteria in Sheets?
No. Google Sheets evaluates the array comparisons in this formula without a special array-entry shortcut.
Is this lookup case-sensitive?
No. Standard equality comparisons in Google Sheets are not case-sensitive. If capitalization must distinguish values, use a case-sensitive method such as EXACT within a suitable array-based approach.
Why does my formula return #N/A even though the values look the same?
Check for hidden spaces, text-versus-number differences, mismatched ranges, or a criterion that points to the wrong cell. Also confirm that one row actually meets every condition at once.
Related
- Where Is the Subscript Button in Excel? Find It Fast
- Where Is AutoFit in Excel? Find the Column and Row Tools
- General in Excel: Find and Use the Number Format Setting
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Share a Calendar in Outlook by Email and Set AccessTo share a live Outlook calendar, open Calendar, choose Share or Share Calendar, enter the recipient’s email address,…
- Find and Unmerge Merged Cells in Excel: Step-by-StepTo find and unmerge cells in Excel for Microsoft 365 on Windows, open Find with Ctrl+F, choose Options…
- Protect a Sheet in Excel: Steps, Options, and LimitsIn Excel for Microsoft 365 on Windows, protect a sheet by opening the worksheet, choosing Review > Protect…
- Cover Page in Microsoft Word: Add and Customize OneTo add a cover page in Microsoft Word for Microsoft 365 on Windows, open the document and choose…
- Excel Highlight Dates Older Than Today AutomaticallyYes. In Excel for Microsoft 365 on Windows, select the date cells, choose Home > Conditional Formatting >…
- Google Sheets Jira Sync: Methods, Setup, and LimitsGoogle Sheets does not include a built-in, always-live Jira sync, but you can connect the two with a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.