XLOOKUP 多条件查找：Excel 与 Google Sheets

Tested in: Live formulas in Google Sheets, November 12, 2026. Microsoft’s XLOOKUP documentation describes array arguments in Excel 365; those unwrapped array forms were not re-tested in a desktop Excel workbook on this date.

Which versions this applies to

• Excel for Microsoft 365, Excel 2021 and Excel 2024: XLOOKUP can take array expressions for the lookup and return ranges. You will often see =XLOOKUP(F2&G2, A2:A6&B2:B6, D2:D6) written without ARRAYFORMULA. Confirm that form in your own Excel file; it returned #VALUE! in Google Sheets until ARRAYFORMULA was added.
• Excel 2019 and earlier: No XLOOKUP. Use INDEX/MATCH with the same boolean array, entered as an array formula in older Excel, or a helper column.
• Google Sheets: XLOOKUP is available. Wrap the combined lookup array in ARRAYFORMULA, as in the formulas above.

The sample list

West/Bolt appears twice, which is useful later: XLOOKUP returns the first match unless you change search_mode. F2=West, G2=Bolt, F3=East, G3=Widget, and F4=East, G4=Bolt (a pair that is not in the list).

Method 1: Concatenate the two keys

Join the two lookup values, and join the two columns in the same way:

=XLOOKUP(F2&"|"&G2, ARRAYFORMULA(A2:A6&"|"&B2:B6), D2:D6)

That returned 0.55 for West/Bolt and 12.5 for East/Widget. The helper keys themselves spilled as East|Widget, East|Gasket, West|Bolt, and so on.

A missing pair gets #N/A unless you supply the fourth argument:

=XLOOKUP(F4&"|"&G4, ARRAYFORMULA(A2:A6&"|"&B2:B6), D2:D6, "not found")

East/Bolt is not in the list; this returned not found.

The delimiter matters. Joining with no separator (F2&G2 and A2:A6&B2:B6) also returned 0.55 and 12.5 on this particular list, because no two-column pair produced the same string as a different pair. That will not always be true. East + Bolton and EastB + olton would collide. A character that does not appear in the data, such as |, avoids that.

Without ARRAYFORMULA, =XLOOKUP(F2&G2, A2:A6&B2:B6, D2:D6) returned #VALUE! in this Sheets file. The & on two ranges does not automatically produce a column of keys unless you wrap it.

Method 2: Multiply two TRUE/FALSE tests

Each test produces TRUE or FALSE. Multiplying them gives 1 only on rows where both are true, and 0 everywhere else. XLOOKUP then looks for the 1:

=XLOOKUP(1, ARRAYFORMULA((A2:A6=F2)*(B2:B6=G2)), D2:D6)

This also returned 0.55 for West/Bolt. Without ARRAYFORMULA it returned #VALUE!, the same as the unwrapped concatenate form.

This method is easier to extend to three conditions: add another *(C2:C6=H2). It also avoids the delimiter question, because nothing is being concatenated.

INDEX/MATCH uses the same boolean array. This returned 0.55 as well:

=INDEX(D2:D6, MATCH(1, ARRAYFORMULA((A2:A6=F2)*(B2:B6=G2)), 0))

The 0 in MATCH is required so the 1 is an exact match, not an approximate one. That trap is covered in XLOOKUP vs INDEX MATCH vs VLOOKUP.

First match, last match, or every match

Default XLOOKUP, and MATCH, return the first row that qualifies. West/Bolt has Ben at 0.55 and later Cara at 0.60. Looking up the rep with search_mode omitted returned Ben. Asking XLOOKUP to search from the bottom returned Cara:

=XLOOKUP(1, ARRAYFORMULA((A2:A6="West")*(B2:B6="Bolt")), C2:C6)
=XLOOKUP(1, ARRAYFORMULA((A2:A6="West")*(B2:B6="Bolt")), C2:C6, "none", 0, -1)

If you need every matching row, XLOOKUP is the wrong tool. FILTER returned both amounts, 0.55 and 0.60, and both reps, Ben and Cara:

=FILTER(D2:D6, (A2:A6=F2)*(B2:B6=G2))
=FILTER(C2:C6, (A2:A6="West")*(B2:B6="Bolt"))

Leave empty cells under the formula. When this FILTER was first placed in a column that already had values in the cells below, Sheets returned #REF! (array result was not expanded) instead of the two amounts. That collision is the same class of error described in Fix “Array Result Was Not Expanded Because It Would Overwrite Data”.

A helper column is still valid

If you do not want ARRAYFORMULA in the lookup itself, add a key column:

=A2&"|"&B2

Fill it down, then run a normal XLOOKUP on that column. The math is the same as method 1; the work is just visible in the sheet. This is often the right choice when other people will edit the file, or when the file has to open in an Excel version that is awkward with arrays.

Excel vs. Google Sheets

Argument separators follow the spreadsheet locale (comma vs semicolon).

Common mistakes

1. Forgetting ARRAYFORMULA in Google Sheets. Both unwrapped forms returned #VALUE! here. That error is easy to misread as a bad lookup value; it is the array expression, not the SKU.
2. Concatenating without a delimiter. It worked on this short list and will eventually collide on a real one. Use | or another character that does not appear in the data.
3. Leaving MATCH’s third argument off. Approximate MATCH can return the wrong row and no error. Always pass 0 here.
4. Expecting XLOOKUP to return every West/Bolt row. It returns one. Use FILTER for the full set, and keep the spill range empty.
5. Using IFERROR to hide a #VALUE! from a missing ARRAYFORMULA. You want to see that error until the array is wrapped. IFERROR vs IFNA covers which wrapper hides which error.

FAQ

Can XLOOKUP use two conditions?

Not as two separate arguments. Combine the conditions first, either by concatenating the keys or by multiplying TRUE/FALSE tests, then look up that single result. Both methods returned 0.55 for West/Bolt in this test.

Why do I get #VALUE! from a two-column XLOOKUP in Google Sheets?

If you wrote A2:A6&B2:B6 or (A2:A6=F2)*(B2:B6=G2) without ARRAYFORMULA, that is the likely cause. Both unwrapped forms returned #VALUE! here; adding ARRAYFORMULA produced the amount.

How do I return the last row that matches both conditions?

Set XLOOKUP’s search_mode to -1. For West/Bolt, that changed the rep from Ben (first row) to Cara (later row).

Should I use FILTER instead?

Use FILTER when you want every matching row. It returned both West/Bolt amounts, 0.55 and 0.60. Use XLOOKUP when you want one value, optionally the first or the last.

What if one of the pair is missing?

XLOOKUP returns #N/A. Add the fourth argument for a label, as in the East/Bolt test, which returned not found. If #N/A appears when you can see the pair, check spaces and text-versus-number mismatches; those causes are in XLOOKUP Returns #N/A Even Though the Value Exists.

Related tutorials

• XLOOKUP vs INDEX MATCH vs VLOOKUP: Which Should You Use in 2026?
• XLOOKUP Returns #N/A Even Though the Value Exists: 7 Fixes
• IFERROR vs IFNA: Which Should You Use for Lookups?
• Excel & Google Sheets Lookup Errors: The Complete Troubleshooting Guide

Live-test findings

All numeric results above come from one Google Sheets file dated November 12, 2026. Concatenate and boolean XLOOKUP both returned 0.55 for West/Bolt and required ARRAYFORMULA (#VALUE! without it). A | delimiter produced the same 0.55 and 12.5 results, and not found for East/Bolt. search_mode -1 changed the West/Bolt rep from Ben to Cara. FILTER returned 0.55 and 0.60, and Ben and Cara, once the spill range was empty.

Sources

• Microsoft Support: XLOOKUP function (array arguments, match_mode, search_mode, if_not_found): https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
• Microsoft Support: FILTER function: https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759
• Google Docs Editors Help: XLOOKUP, ARRAYFORMULA, FILTER, INDEX, MATCH: https://support.google.com/docs/answer/12405947, https://support.google.com/docs/answer/3093275, https://support.google.com/docs/answer/3093197, https://support.google.com/docs/answer/3098242, https://support.google.com/docs/answer/3093378