Excel AND in MATCH: Find Rows With Multiple Criteria

Formulas & functions · Updated November 18, 2026

To use AND conditions with Excel MATCH, compare each criteria range with its requested value and multiply the resulting TRUE/FALSE arrays. With regions in A2:A6, products in B2:B6, and the criteria North and Sensor in H2 and H3, =MATCH(1,(A2:A6=H2)*(B2:B6=H3),0) returns 5: the matching row’s position within A2:A6, not its worksheet row number.

What AND in MATCH means

MATCH searches one row or column for a value and returns the position where it finds a match. It does not, by itself, accept a list of separate conditions joined by the AND function. To require that a row meet multiple criteria, you can build a TRUE/FALSE test for each condition and combine those tests so that only rows meeting all of them qualify.

This is useful when you need to find a record using more than one field: a customer and invoice number, a product and size, or a department and month. The logic is “this row meets condition one AND condition two.” In the formula above, the multiplication turns TRUE into 1 and FALSE into 0. A row produces 1 only if both tests are TRUE, so MATCH searches for 1 with its final argument set to 0 for an exact match.

Keep the result of MATCH in mind: it is a position relative to the searched range. If the match is the fifth item in A2:A6, MATCH returns 5 even though that item is in worksheet row 6. Use INDEX with MATCH if you want a value from another column.

Set up a small example

Imagine a sheet with one record per row and these fields:

  • A2:A6 contains North, South, North, West, North.
  • B2:B6 contains Widget, Widget, Cable, Widget, Sensor.
  • C2:C6 contains 12, 9, 15, 11, 18.
  • H2 contains North, and H3 contains Sensor.

The desired record is in row 6: its region is North and its product is Sensor. The ranges in the formula must cover corresponding records. A2:A6 and B2:B6 both start and end on the same rows; if one range is shorter or shifted, the comparisons can fail or produce an error.

Because MATCH searches A2:A6, the position 5 corresponds to the fifth item in that range, which is worksheet row 6. It is not an absolute row number, and it is not the value 18 in column C. This distinction matters when you use the result in another formula.

How the multiple-criteria formula works

For the example, A2:A6=H2 checks each region against North and creates an array of TRUE and FALSE results. Likewise, B2:B6=H3 checks every product against Sensor. Multiplying the arrays applies AND logic row by row: TRUE multiplied by TRUE becomes 1, while any row with a FALSE test becomes 0. MATCH then finds the 1.

The last argument, 0, matters. It tells MATCH to look for an exact match rather than an approximate one. With two criteria, a zero result means that at least one condition was not met on that row; the only rows that can return 1 are rows that pass every test. If more criteria are needed, multiply in another comparison, keeping all ranges the same size.

In Microsoft 365 for Windows, this formula works with dynamic arrays without a special entry command. In older desktop Excel versions that do not calculate this kind of array expression automatically, the formula may need to be confirmed as an array formula with Ctrl+Shift+Enter on Windows or Control+Shift+Return on Mac. If you use an older version, check its behavior before copying the formula widely.

Return a value with INDEX and MATCH

To return the units from column C instead of the position, wrap MATCH in INDEX. With the same data layout and North and Sensor in H2 and H3, =INDEX(C2:C6,MATCH(1,(A2:A6=H2)*(B2:B6=H3),0)) returns 18. MATCH supplies position 5, and INDEX returns the fifth item in C2:C6.

This combination separates the search from the result. You can change the return range to another column while leaving the criteria logic intact, provided the return range lines up with the same records. For example, if column D contained a price, using D2:D6 as the INDEX range would return the price from the matching record. If several rows meet the criteria, MATCH finds the first one in the search range; it does not return every match.

INDEX and MATCH are especially helpful when the return column is to the left of the criteria columns, or when you want a lookup that does not depend on a fixed column number. They also make the logic visible: MATCH identifies the row position, and INDEX retrieves a value at that position.

Why the AND function is usually not the answer

A common attempt is to put AND around comparisons of entire columns and then search for TRUE. The problem is that AND normally reduces its inputs to one overall TRUE or FALSE result. It does not return a separate result for every row. As a result, it cannot tell MATCH which individual record meets both conditions.

For row-by-row multiple criteria, use multiplication for AND logic. For OR logic—where either one condition or another can qualify—a common pattern is to add the comparison arrays and search for a positive result. Be careful when combining AND and OR: parentheses determine which tests are grouped together. If the criteria are complicated, a helper column or a more explicit formula can make the intended logic easier to inspect.

There is a related function named COUNTIFS that can check whether any record meets several criteria, but it answers a different question: how many matches are there? It does not return the position of the first matching record. Choose the function based on whether you need a count, a position, or a value from the matching row.

Use a helper column for a simpler lookup

A helper column is an alternative when you prefer a visible combined key or need a formula that is easier to maintain in an older workbook. With regions in A2:A6 and products in B2:B6, enter =A2&"|"&B2 in D2 and fill it down through D6. The key in D6 becomes North|Sensor.

With the combined keys in D2:D6 and the requested region in H2 and product in H3, =MATCH(H2&"|"&H3,D2:D6,0) returns 5. The separator helps distinguish the two fields, but choose a character that does not appear in either field; otherwise, different pairs could form the same combined text. A helper key also needs consistent values, including spaces and spelling, on both sides of the lookup.

This approach can be easier to audit because you can see the constructed key in each row. Its trade-off is that it adds a column and can be fragile if the source fields contain the chosen separator or are formatted inconsistently. For large or frequently changing datasets, a direct multi-criteria formula may be cleaner.

Alternatives and version considerations

If you have Microsoft 365 or Excel 2021, XLOOKUP can return the matching value directly using the same row-by-row tests. With the example ranges and criteria, =XLOOKUP(1,(A2:A6=H2)*(B2:B6=H3),C2:C6,"Not found",0) returns 18. The fourth argument supplies a message if no row matches, and the final 0 requests exact matching. XLOOKUP is available in Microsoft 365 and Excel 2021, but not in older perpetual versions such as Excel 2019.

Use INDEX and MATCH when compatibility with older Excel versions matters, or when you want the position as a separate result. Use XLOOKUP when it is available and a direct return formula is easier to read. Use COUNTIFS when the question is whether a match exists or how many records qualify, and use a helper column when a visible key makes the workbook easier for other people to maintain.

Excel for the web supports many modern formulas, but behavior and editing options can vary by feature and update. If a workbook must work across desktop, web, and older installations, test the actual formulas in the versions your audience uses. Avoid assuming that a formula available in Microsoft 365 will also work in every older Excel release.

Common errors and how to fix them

  • #N/A: No row meets every condition exactly. Check spelling, extra spaces, number-versus-text differences, and whether the criteria cells contain the values you expect.
  • #VALUE! or unexpected results: Make sure all criteria ranges have matching dimensions and align row for row. Avoid comparing A2:A6 with B3:B7.
  • Wrong position: MATCH returns a position within its lookup range, not the worksheet row number. Add the range’s starting row minus one if you specifically need the sheet row.
  • First match only: MATCH returns the first qualifying row. If duplicate records are possible, decide whether the first one is acceptable or use a method designed to return multiple results.
  • Formula displays as text: Check whether the cell is formatted as Text, and confirm that the formula begins with an equals sign. Change the format to General and re-enter the formula if necessary.

One subtle issue is spaces that are hard to notice. For example, North and North followed by a trailing space are different text values. Cleaning the source data is usually better than adding layers of cleanup to every lookup formula. Also confirm that values which look numeric are stored consistently as numbers or as text.

Frequently Asked Questions

Can MATCH use two criteria in Excel?

Yes. Compare each criteria range with its requested value, multiply the tests to apply AND logic, and search for 1 with MATCH’s exact-match option. The ranges must be aligned and the same size.

Does MATCH return the matching cell’s value?

No. MATCH returns the item’s position within the lookup range. Combine it with INDEX to return a value from another aligned range, or use XLOOKUP in a supported Excel version.

Why does AND not work inside MATCH?

AND normally returns one overall TRUE or FALSE for its inputs rather than a separate result for each row. Multiplying comparison arrays gives a row-by-row AND test that MATCH can search.

How do I return all rows that meet both criteria?

MATCH returns only the first qualifying position. In Microsoft 365 or Excel 2021, FILTER can return multiple matching rows; for example, with the same sample ranges and criteria, =FILTER(A2:C6,(A2:A6=H2)*(B2:B6=H3),"Not found") returns the matching record or the message if none exists.

What does the 0 at the end of MATCH mean?

It specifies an exact match. For multi-criteria formulas, use 0 so MATCH searches for a row where all tests combine to 1 rather than attempting an approximate match.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.