How to Look Up a Value With Two Criteria in Excel

To look up a value based on two criteria in Excel, use XLOOKUP with a multiplied test. It works in Microsoft 365, Excel 2021 or later (including Excel 2024), and Excel for the web:

=XLOOKUP(1, (A2:A7=F2)*(B2:B7=G2), C2:C7, "Not found")

Here, the lookup array is the list Excel searches (the multiplied test), and the criteria are the values you want to match (F2 and G2). If your Excel uses semicolons as argument separators, which some regional settings do, replace each comma with a semicolon. On Excel 2019 or older, skip to the INDEX/MATCH or helper column methods below.

Example table and expected results

Use this small table in A1:C7. You can check every result by eye.

Put the employee you want in F2 and the month in G2. The formula goes in H2.

Dan isn't in the table, so the fourth row falls through to the "Not found" text.

How the formula works

Each piece does one small job.

• (A2:A7=F2) compares every name to F2 and returns a column of TRUE/FALSE values.
• (B2:B7=G2) does the same for the month.
• Multiplying the two columns turns TRUE into 1 and FALSE into 0. A row scores 1 only if both tests are TRUE.
• XLOOKUP(1, ...) searches that column of 1s and 0s for the number 1.
• C2:C7 is the return array: the column the answer comes from.
• "Not found" is the optional if_not_found argument, shown when no row scores 1.

For Ben and Feb, the multiplied test produces 0, 0, 0, 1, 0, 0. The 1 is in the fourth position, so XLOOKUP returns the fourth value in C2:C7, which is 11.

All ranges must be the same size. Mixing A2:A7 with C2:C8 gives wrong results or an error.

Why multiply instead of using AND

AND collapses an entire range into a single TRUE or FALSE, so it can't tell you which row matched. Multiplication works row by row and keeps one result per row. (Adding the tests would produce 2 for a double match, which also works if you look up 2, but multiplication is the common convention.)

Why you need no Ctrl+Shift+Enter in 365/2021

Microsoft 365 and Excel 2021 or later calculate whole-range operations natively, a feature called dynamic arrays. Just press Enter. Older versions need the array entry described next.

Method 2: INDEX and MATCH (works in older Excel)

=IFERROR(INDEX(C2:C7, MATCH(1, (A2:A7=F2)*(B2:B7=G2), 0)), "Not found")

MATCH finds the position of the first 1, and INDEX returns the value in that position from C2:C7. The final 0 means exact match.

In Excel 2019 and earlier, confirm the formula with Ctrl+Shift+Enter. Excel wraps it in curly braces, like {=INDEX(...)}. Don't type the braces yourself. If you press only Enter, you may get #N/A or a wrong value. In Microsoft 365 and Excel 2021 or later, plain Enter works.

One caution: IFERROR hides every error, not just "no match." If you mistype a range, you will see "Not found" and may miss the real problem. Test without IFERROR first.

Method 3: Helper column with XLOOKUP or VLOOKUP

A helper column joins the two fields into one key, so a normal lookup can find it. This is the easiest method to audit.

1. In D1, type the heading Key.
2. In D2, enter =A2&"|"&B2 and fill it down to D7. Ben + Feb becomes Ben|Feb.
3. Look up the combined value in the formula cell (H2, with the employee in F2 and month in G2):

=XLOOKUP(F2&"|"&G2, D2:D7, C2:C7, "Not found")

Ben and Feb returns 11, the same as before. Because XLOOKUP takes separate lookup and return arrays, the key column can sit to the right of the hours. This XLOOKUP version needs Microsoft 365 or Excel 2021 or later.

The delimiter (the |) prevents false matches. Without it, "Al" + "2Jan" and "Al2" + "Jan" would both become "Al2Jan". Pick a character that never appears in your data.

VLOOKUP version for any Excel

VLOOKUP can only return values to the right of its lookup column, so the key must be in the first column of the range. Insert a new column at the far left of the table and call it Key. Your data now sits in B:D (Employee in B, Month in C, Hours in D), and your input cells shift one column right, to G2 (employee) and H2 (month). Put the formula in I2.

In A2, enter =B2&"|"&C2 and fill it down. Then:

=VLOOKUP(G2&"|"&H2, A2:D7, 4, FALSE)

The 4 means "return the fourth column of A2:D7", which is Hours. The same shifted layout works with =XLOOKUP(G2&"|"&H2, A2:A7, D2:D7, "Not found"). VLOOKUP and an INDEX/MATCH on the key column work in every Excel version.

The downside of any helper column is an extra column to maintain. If you can't change the sheet's layout, use Method 1 or 2. For more on choosing between the two lookup functions, see XLOOKUP vs VLOOKUP: Which Should You Use?

Method 4: SUMIFS when the result is a number

=SUMIFS(C2:C7, A2:A7, F2, B2:B7, G2)

SUMIFS adds the values in C where both conditions match. With one row per combination, the sum is just that row's value, so Ben and Feb gives 11. It works in all modern Excel versions with no array entry.

Know its limits:

• Numbers only. It can't return text such as a status or a name.
• Not found returns 0, which looks like a real answer. Hours of 0 and "no record" are indistinguishable.
• Duplicates add up instead of returning the first one.

If those limits are fine for your data, it's the simplest option. You can pair it with COUNTIFS to detect missing rows: =IF(COUNTIFS(A2:A7,F2,B2:B7,G2)=0,"Not found",SUMIFS(C2:C7,A2:A7,F2,B2:B7,G2)).

Which method should you use?

Best method for Excel 2016 or 2019

Neither version has XLOOKUP. Use a helper column with VLOOKUP if you can add a column, otherwise INDEX/MATCH with Ctrl+Shift+Enter.

A quick guide for other situations:

• Microsoft 365 or Excel 2021 or later: XLOOKUP boolean.
• Numeric results, one row per combination: SUMIFS.
• Sheet shared with beginners: helper column. It's the easiest to follow.

For other walk-throughs of the XLOOKUP approach, see the guides from Ablebits, Statology and MyExcelOnline.

Common errors and fixes

#N/A when you expect a match. Check these in order:

• Extra spaces. "Ben " doesn't equal "Ben". Clean data with TRIM, or wrap the test: (TRIM(A2:A7)=F2). In INDEX/MATCH, a formula containing TRIM(A2:A7) also needs Ctrl+Shift+Enter in Excel 2019 and earlier.
• Numbers stored as text. If one side is the text "2025" and the other the number 2025, they won't match. Convert with VALUE() or by multiplying by 1.
• Dates versus text. A cell showing 01/15/2026 may be text. Dates are matched by their underlying serial number, so both sides must be real dates.

Wrong result or #VALUE!. Ranges are different sizes. Make A, B and C ranges the same length.

#NAME?. Your Excel version doesn't have XLOOKUP. Use Method 2, or Method 3 with VLOOKUP, or Method 4.

Only one result when you expect several. Lookups return the first match and ignore later duplicates. To see all matches, use FILTER (365/2021 or later) or a pivot table.

Broken results after copying down. Lock the table ranges with dollar signs, such as $A$2:$A$7, so they don't shift.

Case. Excel's = comparison is not case-sensitive, so "ben" matches "Ben".

Variations

These examples use different layouts from the example table above, so each one describes its own cells.

Three criteria

Multiply in another test. Suppose the table has Employee in A, Month in B, Hours in C and a new Region column in D. Put the employee in F2, the month in G2 and the region in I2, with the formula in K2:

=XLOOKUP(1, (A2:A7=F2)*(B2:B7=G2)*(D2:D7=I2), C2:C7, "Not found")

Each extra condition is one more multiplied comparison. Keep every range the same size.

Number or date ranges

Use comparison operators for ranges. Month names like "Jan" can't be compared with >= or <=, so this needs real dates. Use a table with Employee in A, Date in B and Hours in C:

Put the employee in F2, the start date in G2 and the end date in H2. The formula goes in J2:

=XLOOKUP(1, (A2:A7=F2)*(B2:B7>=G2)*(B2:B7<=H2), C2:C7, "Not found")

For Ana, 01/15/2026 to 01/31/2026, the only row that passes all three tests is row 3, so the result is 15. If several rows fit the range, it still returns only the first.

Two-way lookup

When the answer sits where a row header meets a column header, use two MATCH functions:

=INDEX(B2:D7, MATCH(F2, A2:A7, 0), MATCH(G2, B1:D1, 0))

This layout has names down A2:A7 and months across B1:D1, with the values in B2:D7. It differs from the example table, which lists months down a column.

Excel vs Google Sheets

Google Sheets has XLOOKUP, so the boolean formula may work there, but array math can behave differently. Test it on your own data before relying on it.

For INDEX/MATCH with a multiplied test, Sheets usually needs the formula wrapped in ARRAYFORMULA. Pressing Ctrl+Shift+Enter while editing a formula in Sheets adds ARRAYFORMULA for you. Either way, verify the result against a row you can check by hand.

SUMIFS and the helper column method work the same way in Sheets. Sheets also offers FILTER and QUERY, which are good for returning multiple matches. Locale settings can require semicolons instead of commas, as in Excel.

When a different tool is better

A formula is fine for a quick answer. Switch tools when the job grows:

• Many results or frequent duplicates: use FILTER (365/2021 or later) or a pivot table to summarize by both fields.
• Large or recurring imports: use Power Query to merge two tables on multiple columns, or build a table relationship. The merge can be refreshed when the data changes, and it avoids long array formulas in your sheet.
• Lots of reporting: a pivot table with Employee in rows and Month in columns gives you the whole grid instead of one value at a time.

Quick checklist

• On 365 or 2021 or later, use the XLOOKUP boolean formula.
• On older Excel, use a helper column with VLOOKUP, or INDEX/MATCH with Ctrl+Shift+Enter.
• Use SUMIFS only for numeric results.
• Add a not-found message so a blank result isn't mistaken for zero.
• Clean spaces and check text versus numbers if you see #N/A.
• Test with a row you can verify by hand, like Ben + Feb = 11.