Yes. In Microsoft Excel for Microsoft 365, combine the criteria into a TRUE/FALSE test, use MATCH to find the first row where every test is true, and use INDEX to return a value from that row. For example, you can match a salesperson and a region together rather than searching for either one alone.
What MATCH can and cannot do
MATCH finds the position of a lookup value in one lookup array. It does not have separate arguments for a list of criteria, such as “find the row where column A is Ana and column B is East.” The usual solution is to make an array of 1s and 0s from the criteria: a row that satisfies every test becomes 1, and MATCH searches for that 1.
To return a value from the matching row, pair MATCH with INDEX. This is useful when criteria are spread across several columns and the answer is in another column. The formula below uses a small sales table as a running example:
- Column A, rows 2–6: salesperson
- Column B, rows 2–6: region
- Column C, rows 2–6: sales amount
- Cell E2: salesperson to find; cell F2: region to find
The sample rows are Ana, East, 120; Ben, West, 90; Ana, West, 150; Cara, East, 110; and Ana, East, 175. With those values in place, searching for Ana in East returns the amount from the first matching row, 120.
Use INDEX and MATCH for an exact multi-criteria lookup
With salesperson names in A2:A6, regions in B2:B6, amounts in C2:C6, the requested name in E2, and the requested region in F2, enter:
=INDEX(C2:C6,MATCH(1,(A2:A6=E2)*(B2:B6=F2),0))
Each comparison produces an array of TRUE and FALSE values. Multiplication coerces TRUE to 1 and FALSE to 0. A row matching both criteria therefore produces 1; a row failing either test produces 0. The final argument of MATCH is 0, which requests an exact match. INDEX uses the position returned by MATCH to retrieve the corresponding amount.
In the sample data, the combined test is true for rows 2 and 6. MATCH returns the position of the first one, so the formula returns 120, not 175. In Excel for Microsoft 365, enter this formula normally with Enter; it does not need the older Ctrl+Shift+Enter array-formula entry. The ranges being tested must cover the same number of rows.
Return a different column or report no match
The result range does not have to be the amount column. To return a date, product, or other field, point INDEX at that field’s range, keeping its rows aligned with the criteria ranges. A misaligned range can return a value from the wrong record even when the criteria logic itself is correct.
With the same name, region, and amount layout, but with the possibility that no row matches, you can provide a readable fallback:
=IFNA(INDEX(C2:C6,MATCH(1,(A2:A6=E2)*(B2:B6=F2),0)),"No matching row")
If Ana and North are entered as the criteria, for example, the formula displays “No matching row” instead of #N/A. This is often clearer in a report, but it can also hide a mistaken spelling or an incorrectly entered criterion. During setup, consider leaving the error visible until you have confirmed the source data and lookup values.
Use XLOOKUP as a more direct alternative
Excel for Microsoft 365 also includes XLOOKUP, which can use the same combined tests and lets you specify what to display if nothing matches. It is available in Microsoft 365 and Excel 2021 or later, but not in older perpetual editions such as Excel 2019.
With names in A2:A6, regions in B2:B6, amounts in C2:C6, and lookup criteria in E2 and F2, use:
=XLOOKUP(1,(A2:A6=E2)*(B2:B6=F2),C2:C6,"No matching row")
For Ana and East, this returns 120: the first matching amount. The lookup array is the product of the two tests, and the return array is C2:C6. Unlike a bare INDEX/MATCH combination, the fourth argument makes the not-found result explicit. Both methods are useful; choose the one that fits your workbook and the Excel versions used by everyone who opens it.
Count matches or return every matching row
A lookup returns one result, typically the first matching record. If the question is how many records meet all the criteria, use COUNTIFS, which is designed to count rows matching multiple conditions.
With names in A2:A6, regions in B2:B6, and the criteria in E2 and F2, enter:
=COUNTIFS(A2:A6,E2,B2:B6,F2)
For Ana and East in the sample data, the result is 2. Each criteria range is paired with a criterion, and all pairs must be satisfied by a row for it to be counted. This formula is often simpler than MATCH when you need a count rather than a value from a matching row.
To show every matching amount, use FILTER. It is available in Microsoft 365 and Excel 2021 or later, and its results spill into cells below the formula. With the same ranges and criteria cells as above, enter:
=FILTER(C2:C6,(A2:A6=E2)*(B2:B6=F2),"No matching row")
For Ana and East, this returns 120 and 175 in two rows. Make sure the cells where the results need to spill are empty; otherwise Excel can display a #SPILL! error. Use MATCH or XLOOKUP for one result, COUNTIFS for a count, and FILTER when all matching records matter.
Add more criteria or use different tests
For three or more exact-match criteria, add another comparison to the product. Every criteria range must have the same height, and every comparison must refer to the corresponding lookup value. For example, you could add a product type in column D and a requested type in G2.
With names in A2:A6, regions in B2:B6, product types in D2:D6, amounts in C2:C6, and lookup values in E2, F2, and G2, use:
=INDEX(C2:C6,MATCH(1,(A2:A6=E2)*(B2:B6=F2)*(D2:D6=G2),0))
The result is the amount from the first row meeting all three conditions. Multiplication represents “AND”: every test must be true. If the requirement is “this region or that region,” the logic is different; adding Boolean tests can represent “OR,” but the intended result and handling of duplicate matches need to be considered. For many ordinary “either/or” counts, separate COUNTIFS calculations can be easier to understand.
Criteria do not all have to be simple text equals tests. A comparison can test a number against a threshold, such as a date on or after a date in a cell. For example, a test shaped like (D2:D6>=G2) creates TRUE for dates on or after G2. Combine it with the other tests using multiplication for an all-conditions lookup. Dates in Excel are stored as serial numbers, so make sure the source dates and the criterion are actual Excel dates rather than text that only looks like a date.
Why concatenating criteria can be risky
Another approach is to join the criteria into a single text key, such as the name followed by the region, and search for the joined value. It can work for controlled data, but simple concatenation can create collisions: the pair “AB” and “C” produces the same joined text as “A” and “BC.” Separators reduce that risk, but only if the separator cannot appear ambiguously in the source values. The Boolean-product method avoids relying on a constructed text key and makes each condition visible in the formula.
Concatenation can also make number and text values behave differently than expected. A number stored as text may not compare or join in the way a user assumes. If you do choose a helper key, standardize the source fields and use a delimiter that cannot occur in them, or otherwise escape delimiter characters. For most multi-column exact lookups, comparing the original columns separately is easier to audit.
Common errors and practical checks
A #N/A result usually means no row passed all the tests, but it can also point to inconsistent source data. Check for extra spaces, different spellings, numbers stored as text, or a criterion that does not exactly match the cell value. Excel’s ordinary text comparisons are not case-sensitive, so “Ana” and “ANA” count as equal for this lookup. If capitalization must distinguish records, the standard comparison needs a case-sensitive approach such as EXACT, applied to each text criterion.
Also check that the criteria ranges and result range start and end on corresponding rows. A formula comparing A2:A100 with B2:B99 uses arrays of different sizes and will not produce a valid row-by-row test. Avoid using entire-column references inside these array comparisons in a large workbook when a bounded range or Excel Table column reference will do; evaluating over a million rows can add needless calculation work.
- Confirm the lookup cells contain the values you intend to search for.
- Check for leading or trailing spaces in imported text.
- Verify that numbers and dates are stored in a consistent type.
- Confirm all criteria ranges have matching dimensions and aligned rows.
- Decide whether the first match is acceptable when duplicates exist.
- Use
COUNTIFSto check whether the criteria find zero, one, or multiple rows.
Wildcards such as an asterisk can be useful for partial-text searches with functions that support wildcard matching, but the Boolean equality test shown above treats the criterion as a literal value. If you need partial matches across several fields, decide which columns need wildcard behavior and choose a method designed for that requirement rather than assuming the exact-match formula will interpret wildcard characters automatically.
Frequently Asked Questions
Can MATCH search multiple columns at once?MATCH itself searches one lookup array. You can create a calculated array from multiple column tests and search for 1 in it, then combine the position with INDEX to return a corresponding value.
Does MATCH with multiple criteria return the first match?
Yes. The standard exact-match pattern returns the position of the first row that satisfies every condition. If multiple matching records exist and you need them all, use FILTER in Microsoft 365 or Excel 2021 and later.
Do I need Ctrl+Shift+Enter for this formula?
Not in Excel for Microsoft 365 on Windows. Enter the formula normally. Older Excel versions may require Ctrl+Shift+Enter for some formulas that calculate arrays; behavior depends on the particular formula and version.
Is XLOOKUP better than INDEX and MATCH?
Neither is always better. XLOOKUP has a direct not-found argument and can be easier to read, while INDEX and MATCH work in older Excel versions that do not include XLOOKUP. Both can return the first row satisfying combined criteria.
How do I find out whether the criteria match more than one row?
Use COUNTIFS with the same criteria ranges and lookup values. A result of 0 means no rows match, 1 means one row matches, and a number above 1 indicates duplicates that a first-match lookup will not distinguish.
Choosing the right formula
Use INDEX plus MATCH when you want a familiar exact lookup that combines several column tests, or when compatibility with older Excel files matters. Choose XLOOKUP when your workbook supports it and you want a clear not-found result. Use COUNTIFS to count qualifying records, and FILTER to return all of them. The key idea is that the criteria tests must describe the same rows, and the formula should match the output you actually need: one result, a count, or a complete set of records.
Related
- Excel Data Analysis: How to Build a Useful Model
- Remove a Data Model from Excel: Steps and Effects
- Excel Data Modeling: How It Works and When to Use It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Trim Empty Cells: Remove Blank Rows and ColumnsExcel has no single “trim empty cells” command. To remove blank cells from a selected range, select the…
- Excel IFS in Conditional Formatting: How to Use ItYes. In Excel for Microsoft 365 on Windows, you can use the IFS function in a conditional-formatting formula,…
- Calculate Percentage in Excel: Formulas for a ColumnTo calculate a percentage in an Excel column, divide each part by its whole, then format the results…
- How to Copy Formula Data in Excel Without Breaking ReferencesTo copy formula data in Excel, select the cells, press Ctrl+C, choose the destination, and use Home >…
- Excel INDEX Function: Chinese Data, Syntax, and ExamplesIn Excel, INDEX returns a value from a specified position in a range, and it works with Chinese…
- Excel AND Function in an IF Statement: How It WorksYes. In Excel, put AND inside IF when the result should depend on several conditions being true at…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.