XLOOKUP、INDEX/MATCH 與 VLOOKUP：2026 年該用哪個？

Tested in: Live formulas in Google Sheets on November 12, 2026, using the catalog below. Excel availability and default-argument notes come from Microsoft’s XLOOKUP, VLOOKUP, INDEX and MATCH documentation. This is not a new desktop Excel workbook test on this date.

Which versions this applies to

• Excel for Microsoft 365, Excel 2021 and Excel 2024: XLOOKUP, INDEX/MATCH and VLOOKUP are all available.
• Excel 2019, 2016 and earlier: There is no XLOOKUP. Microsoft’s XLOOKUP documentation states this directly. INDEX/MATCH and VLOOKUP are the options; a file that uses XLOOKUP will show #NAME? when opened in those versions.
• Google Sheets: XLOOKUP, INDEX, MATCH and VLOOKUP are all available. Argument order for XLOOKUP matches Excel: lookup value, lookup range, return range, then the optional if_not_found, match_mode and search_mode arguments.

The sample catalog

The formulas in this article use this table, with the lookup value SKU-103 in F2:

A second copy of the same list later in the sheet adds a repeated SKU-103 at 0.60 so last-match behavior can be checked.

Decision table

“Faster” is not a good reason to pick one of these for a few thousand rows. Time the file if a lookup is actually slow; do not assume VLOOKUP is faster or slower from a rule of thumb.

The default-match trap

This is the difference that causes silent wrong answers.

XLOOKUP’s default match is exact. Looking up a missing numeric ID 1004 in a sorted list of 1001, 1002, 1003 and 1005 returned #N/A.

VLOOKUP’s fourth argument defaults to TRUE (approximate). The same lookup returned Charlie, the name that belongs to 1003. No error appeared.

MATCH’s third argument defaults to 1 (also approximate). =MATCH(1004, A21:A24) returned 3, the position of 1003. =MATCH(1004, A21:A24, 0) returned #N/A.

=VLOOKUP(1004, A21:C24, 2, TRUE)    → Charlie
=VLOOKUP(1004, A21:C24, 2, FALSE)   → #N/A
=MATCH(1004, A21:A24)               → 3
=MATCH(1004, A21:A24, 0)            → #N/A
=XLOOKUP(1004, A21:A24, B21:B24)    → #N/A

If you use INDEX/MATCH, write the 0. If you keep VLOOKUP, write FALSE. Approximate match is the right tool for a sorted range table, such as tax brackets or quantity discounts. It is the wrong tool for IDs, SKUs and names. For a longer walkthrough of that VLOOKUP failure, see VLOOKUP Returning the Wrong Value? The Approximate Match Trap.

Looking left

VLOOKUP can only return a column to the right of the first column in its range. To get the SKU for the product Bolt, this formula searched column A, found no “Bolt”, and returned #N/A:

=VLOOKUP("Bolt", A2:C5, 1, FALSE)

XLOOKUP and INDEX/MATCH do not care which column is on the left:

=XLOOKUP("Bolt", B2:B5, A2:A5)
=INDEX(A2:A5, MATCH("Bolt", B2:B5, 0))

Both returned SKU-103.

You can rearrange columns to make VLOOKUP work. That is a workaround, not a reason to choose it for new files.

Missing values

A missing SKU-104 produced #N/A from all three functions. XLOOKUP can replace that in the fourth argument, without wrapping the formula:

=XLOOKUP(F3, A2:A5, C2:C5, "Not in catalog")

That returned Not in catalog. INDEX/MATCH and VLOOKUP need IFNA for the same display:

=IFNA(INDEX(C2:C5, MATCH(F3, A2:A5, 0)), "Not in catalog")
=IFNA(VLOOKUP(F3, A2:C5, 3, FALSE), "Not in catalog")

Use IFNA rather than IFERROR when the only error you want to hide is #N/A. IFERROR also swallows #REF! and #NAME?, which hides a broken range or a typo. Both behaviors were checked: IFERROR turned a five-column VLOOKUP #REF! into the word “hidden”; IFNA left the #REF! visible.

A #N/A that you did not expect usually means the two cells are not actually equal: a trailing space, a non-breaking space, or a number stored as text. SKU-103 with a trailing space (LEN was 8, not 7) made XLOOKUP return #N/A; TRIM of the same value returned 0.55. A text 1003 did not match a numeric 1003 until both sides were coerced. In Google Sheets, the range coercion needed ARRAYFORMULA:

=XLOOKUP(F28&"", ARRAYFORMULA(A28:A29&""), B28:B29)

That returned Charlie-num. Writing A28:A29&"" without ARRAYFORMULA still returned #N/A in this Sheets test. =XLOOKUP(VALUE(F28), A28:A29, B28:B29) also worked, but only because those IDs are true numbers with no leading zeros. For the full #N/A checklist, see XLOOKUP Returns #N/A Even Though the Value Exists: 7 Fixes.

First match vs last match

VLOOKUP and MATCH return the first matching row from the top. So does XLOOKUP when search_mode is omitted or set to 1. In a list where SKU-103 appears at 0.55 and later at 0.60:

=XLOOKUP("SKU-103", A13:A16, C13:C16, "Not found", 0, 1)
=INDEX(C13:C16, MATCH("SKU-103", A13:A16, 0))
=XLOOKUP("SKU-103", A13:A16, C13:C16, "Not found", 0, -1)

The first two returned 0.55. The third, with search_mode -1, returned 0.60.

There is no equally direct “search from the bottom” switch on VLOOKUP or on classic MATCH. If you need the latest row and you are stuck on Excel 2019, you need a different construction (often a helper column or an array formula), not a fourth-argument change.

Returning more than one column

XLOOKUP can return several columns in one formula. This spilled Bolt, 0.55 and West across three cells:

=XLOOKUP(F2, A2:A5, B2:D5)

Leave the cells to the right empty. If something is already sitting in the spill range, you will get a spill or overwrite error instead of the three values.

INDEX returns one range at a time unless you wrap several INDEX calls. VLOOKUP returns one column, identified by a number that has to be updated if someone inserts a column inside the table. That column-index breakage is a common source of “the formula still works, but the value is from the wrong field.”

The lookup range and the return range should be the same height. In this Google Sheets test, =XLOOKUP(F2, A2:A5, C2:C6) returned #N/A. Microsoft’s XLOOKUP documentation describes a #VALUE! error in Excel when those arrays are different sizes. Treat the error name as platform-specific, and fix the ranges rather than wrapping the formula.

Wildcards and case

With match_mode 2, =XLOOKUP("SKU-10*", A2:A5, B2:B5, "none", 2) returned Widget, the product for the first SKU that starts with SKU-10. VLOOKUP with FALSE also treats * and ? as wildcards. If you need a literal asterisk in the key, that has to be escaped; it is easy to miss.

Lowercase sku-103 matched SKU-103 in XLOOKUP, VLOOKUP and INDEX/MATCH; all three returned 0.55. None of these is case-sensitive in normal use. If codes differ only by case, use an EXACT-based INDEX/MATCH, as described in the VLOOKUP wrong-value article.

Excel vs. Google Sheets

Argument separators follow the spreadsheet locale. A comma in these examples may need to be a semicolon in a European locale.

Common mistakes

1. Copying a VLOOKUP and forgetting FALSE. The formula looks fine and may even return a plausible number.
2. Using MATCH without 0. INDEX then points at a nearby row, not an exact ID.
3. Assuming XLOOKUP exists in every Excel file. Recipients on Excel 2016 or 2019 will see #NAME?.
4. Using IFERROR to “clean up” lookup results. It will also hide a typo in the function name or a range that no longer exists. Prefer IFNA, or leave #N/A visible until you know why it appeared.
5. Comparing a text ID with a numeric ID. They look identical and still fail an exact match.

FAQ

Is XLOOKUP better than INDEX/MATCH?

For new work in Excel 365/2021/2024 or Google Sheets, yes: the default is exact, the “not found” text is built in, and last-match search is a single argument. INDEX/MATCH is the better choice when the workbook has to run in Excel 2019 or earlier.

Why does VLOOKUP return a value for an ID that does not exist?

The fourth argument is missing or TRUE, so VLOOKUP does an approximate match and returns the closest smaller key. Add FALSE, or switch to XLOOKUP.

Can VLOOKUP look left?

Not without rearranging the table so the key is in the first column of the VLOOKUP range. Use XLOOKUP or INDEX/MATCH instead.

How do I get the last matching row?

In XLOOKUP, set search_mode to -1. In this test, that changed the price of SKU-103 from 0.55 (first row) to 0.60 (later row). VLOOKUP and standard MATCH do not have that switch.

Will XLOOKUP work in Excel 2019?

No. Microsoft’s documentation says XLOOKUP is not available there. Use INDEX/MATCH, and remember the exact-match 0 in MATCH.

Related tutorials

• Excel & Google Sheets Lookup Errors: The Complete Troubleshooting Guide
• XLOOKUP Returns #N/A Even Though the Value Exists: 7 Fixes
• VLOOKUP Returning the Wrong Value? The Approximate Match Trap (and 4 Other Causes)
• Convert Numbers Stored as Text in Excel and Google Sheets (5 Methods)

Live-test findings

All numeric results in this article come from one Google Sheets file dated November 12, 2026. XLOOKUP, INDEX/MATCH and exact VLOOKUP agreed on 0.55 for SKU-103. Approximate VLOOKUP for the missing ID 1004 returned Charlie (the 1003 row) with no error. XLOOKUP search_mode -1 returned the later 0.60 price. A text 1003 did not match a numeric 1003 until ARRAYFORMULA coerced the ID column. An XLOOKUP return range one row taller than the lookup range returned #N/A.

Sources

• Microsoft Support: XLOOKUP function (availability, argument order, match_mode, search_mode, if_not_found, array size #VALUE!): https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
• Microsoft Support: VLOOKUP function (range_lookup default, exact vs approximate): https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1
• Microsoft Support: MATCH function (match_type default 1): https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a
• Google Docs Editors Help: XLOOKUP function: https://support.google.com/docs/answer/12405947
• Google Docs Editors Help: VLOOKUP: https://support.google.com/docs/answer/3093318
• Google Docs Editors Help: MATCH: https://support.google.com/docs/answer/3093378