To find a row that matches two criteria in Excel, use XLOOKUP with a condition for each column: =XLOOKUP(1,(A2:A100=E2)*(B2:B100=F2),C2:C100,"Not found"). With IDs in column A, months in B, values to return in C, and the two search values in E2 and F2, this returns the value from the first row where both conditions are true.
What a double match means
People use “double match” to mean a few related things in Excel. Most often, they want to look up a record using two fields together—for example, find the sales amount for both a particular employee and a particular month. A match on just one field may return the wrong record when that field appears more than once.
A two-criteria lookup checks both conditions on the same row. If the employee is in one column and the month is in another, Excel should return a result only when the employee and month both match. This is different from finding a duplicate value, comparing two lists, or looking for a value at the intersection of a row and a column. The formulas below focus first on the common two-criteria lookup, then cover those nearby needs.
Set up the data and criteria
For the examples, imagine a worksheet with employee IDs in A2:A100, months in B2:B100, and sales amounts in C2:C100. Put the ID you want to find in E2 and the month in F2. For example, the records might include ID 104 for January with sales of 1,250, ID 104 for February with sales of 1,425, and ID 217 for January with sales of 980. Searching for ID 104 and February should return 1,425.
The two criteria need to be comparable with the source values. If the source contains actual Excel dates formatted to display as months, enter a real date as the criterion, not the text “February.” If the source contains month names as text, the criterion should use the corresponding text. Likewise, an ID stored as a number may not match an ID stored as text, even if both cells look the same on screen.
Keep the lookup ranges the same size and aligned by row. A2:A100, B2:B100, and C2:C100 all cover the same records. If one range starts on row 2 and another on row 3, Excel may compare fields from different records and return a misleading answer.
Return one result with XLOOKUP
In Microsoft 365 for Windows, the simplest modern approach is to test each criterion, multiply the tests, and ask XLOOKUP to find 1. Each comparison produces TRUE for a match and FALSE otherwise. In arithmetic, TRUE behaves like 1 and FALSE like 0, so multiplication produces 1 only when both tests are true. If either condition fails, the product is 0.
With IDs in A2:A100, months in B2:B100, results in C2:C100, the requested ID in E2, and requested month in F2, enter this formula in the cell where you want the answer: =XLOOKUP(1,(A2:A100=E2)*(B2:B100=F2),C2:C100,"Not found"). If E2 contains 104 and F2 contains February, and row 3 contains those values with 1,425 in C3, the result is 1,425. If no row meets both conditions, the formula displays Not found.
XLOOKUP uses exact matching by default, which suits IDs and category labels. It returns the first matching row in the lookup range. If the same ID-and-month combination occurs more than once, it does not automatically warn you; it returns the first result. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. The formula syntax is the same in current Excel for Mac, although keyboard shortcuts and some interface details differ.
Return every row that matches
If repeated combinations are possible and you need to see all of them, use FILTER rather than XLOOKUP. FILTER returns an array of matching results, which spills into neighboring cells. Leave enough empty space below the formula, or Excel may show a spill error because another cell blocks the output.
With IDs in A2:A100, months in B2:B100, sales in C2:C100, and the search values in E2 and F2, enter =FILTER(C2:C100,(A2:A100=E2)*(B2:B100=F2),"No match"). If two rows meet the conditions and their sales values are 1,425 and 1,500, the formula returns both values in a vertical spill range. If none do, it returns No match. FILTER is available in Microsoft 365 and Excel 2021 or later.
To return full matching records instead of just the sales column, make the first FILTER argument a multi-column range, such as A2:C100. The result will include the ID, month, and sales for every matching row. This is useful for auditing duplicate records because it lets you inspect the entire matching entry rather than only one field.
Count matches or add their values
Sometimes “double match” means checking whether a particular combination exists, not retrieving a value. COUNTIFS counts rows that meet multiple conditions. With IDs in A2:A100, months in B2:B100, and criteria in E2 and F2, use =COUNTIFS(A2:A100,E2,B2:B100,F2). If exactly one row has ID 104 and February, the result is 1; if two rows do, the result is 2. A result of 0 means no row meets both criteria.
COUNTIFS is also a useful duplicate check. A count greater than 1 means the combination appears multiple times, though it does not identify which rows are duplicates. You can use conditional formatting or a separate filter to inspect them. For a yes-or-no check, interpret any count above zero as a match.
If the goal is to total values across every matching row, use SUMIFS instead of looking up a single result. With sales in C2:C100, IDs in A2:A100, months in B2:B100, and criteria in E2 and F2, enter =SUMIFS(C2:C100,A2:A100,E2,B2:B100,F2). If two matching rows contain 1,425 and 1,500, the result is 2,925. SUMIFS adds all matching values; it does not select the first row. That distinction matters when duplicate records are valid separate transactions.
Use a method for older Excel versions
XLOOKUP and FILTER are not available in some older Excel releases. One practical alternative is a helper key: create a new column that joins the two fields, then use a traditional lookup on that combined value. A separator such as a vertical bar helps keep the fields distinct. The separator should not itself appear in either field, or different pairs could create the same combined key.
For a separate helper-key layout, put the combined key in A2:A100, the original ID in B, the month in C, the result in D, and the two requested criteria in F2 and G2. In A2, use =B2&"|"&C2 and fill it down. Then, in the result cell, use =VLOOKUP(F2&"|"&G2,A2:D100,4,FALSE). If the key for 104 and February is in A3 and D3 contains 1,425, the lookup returns 1,425. FALSE requests an exact match. This setup works in older versions that support VLOOKUP, but it still returns only the first matching key and can return an error when no key exists.
Another older-version option is INDEX and MATCH with two conditions, but some versions require entering the formula as an array formula with Ctrl+Shift+Enter. The helper-column approach is often easier to review and troubleshoot, especially for a shared workbook. For a workbook used by people on different Excel versions, check which functions their version supports before replacing a compatible formula.
Common problems and how to fix them
A formula can look correct and still fail because the source values are inconsistent. Check these common causes before changing the lookup logic:
- Text versus numbers: An ID entered as text may not equal the same digits stored as a number. Use consistent data types in the source and criteria cells.
- Extra spaces: A trailing space in a name or category prevents an exact text match. TRIM can remove ordinary leading and trailing spaces, but imported nonbreaking spaces may need separate cleanup.
- Dates that only look alike: A displayed month may represent a full date. Compare actual dates or use a consistent month field rather than comparing a date value to a month-name string.
- Different range sizes: Keep each criteria range and the return range aligned to the same first and last data rows.
- Duplicate combinations: XLOOKUP returns the first match. Use FILTER to see every result, or COUNTIFS to count the repeated combination.
Text matching with the equality tests in these examples is not case-sensitive: “North” and “NORTH” are treated as equal. If capitalization must distinguish records, ordinary XLOOKUP criteria are not enough; the comparison needs a case-sensitive approach such as EXACT used in a suitable array formula. Also remember that formulas recalculate when source values change, so a result can change if a new matching row is inserted above an existing one.
Choose the right kind of double match
Pick the formula based on what the answer should represent, rather than using a lookup formula for every task:
- Use XLOOKUP when you need one result from the first matching row.
- Use FILTER when you need all matching rows or want to inspect duplicates.
- Use COUNTIFS when you need the number of rows meeting both conditions.
- Use SUMIFS when you need the total of a numeric field across all qualifying rows.
- Use a helper key and VLOOKUP when compatibility with older Excel versions is more important than avoiding a helper column.
If you actually mean a two-way lookup—choosing one row label and one column heading in a grid—that is a different setup. For example, finding February sales for one employee in a table with employees down the rows and months across the columns requires matching a row and a column position. The two-condition XLOOKUP formula is intended for data stored as records in rows, with each condition in its own field.
Frequently Asked Questions
Can Excel match two columns at the same time?
Yes. Test each criteria column and combine the tests, as in the XLOOKUP example above. Both conditions must be true on the same row for it to return a result.
Does XLOOKUP return all double matches?
No. XLOOKUP returns one match, specifically the first matching row by default. Use FILTER to return all matching rows in Microsoft 365 or Excel 2021 and later.
How do I know whether the two-value combination is duplicated?
Use COUNTIFS with the two criteria ranges and criteria cells. A result greater than 1 means that combination appears on more than one row.
Can I match three criteria instead of two?
Yes. Add another comparison inside the multiplication expression, and add the corresponding range and criterion to COUNTIFS or SUMIFS. Keep all ranges aligned to the same records.
Why does my formula show Not found when the values look identical?
Check for text-versus-number differences, extra spaces, and dates stored as serial values rather than text. Also confirm that the criteria cells contain the intended values and that every range covers the same rows.
Related
- Commercial Invoice Template in Excel: What to Include
- Commission Calculation Software in Excel: A Practical Guide
- Commission Calculator Excel Template: Build One That Fits
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel ABS Function: How to Return a Positive ValueTo turn a negative number into a positive one in Excel, use the ABS function: it returns the…
- Array Formula in Excel: Examples, Uses, and Key TipsAn Excel array formula works with multiple values at once, either returning one result or spilling a set…
- Excel Formula for Balance Remaining: Simple ExamplesThe basic Excel formula for balance remaining is =B2-C2: subtract the amount paid in C2 from the original…
- Excel Average by Range: Formula & ExamplesTo average a range of cells in Excel, use the AVERAGE function: =AVERAGE(A1:A10) averages all numeric values in…
- Excel Custom Number Format: Round Values to ThousandsTo display Excel values rounded to the nearest thousand without changing the stored numbers, apply the custom number…
- Excel TRIM LEN Formula: Count Characters After CleanupTo count the characters in text after removing extra spaces, put the text in A2 and enter =LEN(TRIM(A2))…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.