VLOOKUP vs XLOOKUP vs INDEX MATCH in Google Sheets: 10 Tests

Google Sheets has three common ways to look up a value: VLOOKUP, XLOOKUP and INDEX with MATCH. All three find “Cherry” and return its units. They differ in what happens when something goes wrong: a column is inserted, the data isn’t sorted, the value appears twice, or there are two conditions. Several of those cases return a wrong answer without any error, so we ran all three functions through the same ten tests.

The main table, in A1:E9, holds Region, Product, Month, Units and Price; it’s the same table we use in XLOOKUP with multiple criteria. Apple appears four times and Cherry once (East, Feb, 3 units).

Tested in Google Sheets. We ran more than 40 formulas for this comparison on November 12, 2026, on a table we then changed by inserting a column. Error messages are copied word for word. How we test

The short answer

Test VLOOKUP XLOOKUP INDEX MATCH
1. Basic exact lookup Yes Yes Yes
2. A column is inserted later Wrong value, no error Still right Still right
3. Return a column to the left No Yes Yes
4. Exact match by default No: wrong value if you forget FALSE Yes No: needs the 0
5. Your own “not found” text With IFNA Built in With IFNA
6. Last match No Yes No
7. Several conditions No Yes, inside ARRAYFORMULA Yes
8. Several result columns Inside ARRAYFORMULA Yes Not directly
9. Nearest value, unsorted data Wrong value Right Wrong value
10. Wildcards such as Ch* Always on Only with match mode 2 Always on

Our recommendation: use XLOOKUP for new sheets. Use INDEX MATCH when you look up with several conditions and would rather not wrap the formula in ARRAYFORMULA. Keep VLOOKUP for simple, stable tables, and always end it with FALSE. If you’d like a formula written for your columns, try our lookup formula generator.

1. Basic exact lookup

All three returned 3, the units for Cherry:

=VLOOKUP("Cherry", B2:E9, 3, FALSE)
=XLOOKUP("Cherry", B2:B9, D2:D9)
=INDEX(D2:D9, MATCH("Cherry", B2:B9, 0))

The difference is in how you point to the answer. VLOOKUP takes a block and a column number counted from its first column (3 means the third column of B:E, which is D). XLOOKUP and INDEX MATCH take the result column itself.

2. A column is inserted later

This is the test that matters most for a sheet that changes over time. In a copy of the table, we inserted a new column between Product and Month, then read the same three formulas again. Google Sheets adjusted every range on its own:

Formula after the insert Result
=VLOOKUP("Cherry", B2:F9, 3, FALSE) Feb, no error
=XLOOKUP("Cherry", B2:B9, E2:E9) 3
=INDEX(E2:E9, MATCH("Cherry", B2:B9, 0)) 3

The range in VLOOKUP grew to B:F, but the column number stayed 3, which now points to Month. Nothing in the sheet warns you. Dollar signs in the range didn’t help either: $B$2:$E$9 also returned Feb.

If you keep VLOOKUP, let it find the column number from the header instead of typing it: =VLOOKUP("Cherry", B1:F9, MATCH("Units", B1:F1, 0), FALSE) returned the correct 3 after the insert.

Found in testing: the inserted-column problem and the missing FALSE (test 4) both produced believable values from the wrong place, never an error. If a VLOOKUP result looks off, check the column number and the last argument before anything else.

3. Return a column to the left

The region (column A) is to the left of the product (column B). VLOOKUP only searches the first column of its range, so it can’t do this lookup. Starting the range at A, =VLOOKUP("Cherry", A2:E9, 1, FALSE) searched for Cherry among the regions and returned #N/A, Did not find value ‘Cherry’ in VLOOKUP evaluation.

XLOOKUP and INDEX MATCH don’t care about the direction. Both returned East:

=XLOOKUP("Cherry", B2:B9, A2:A9)
=INDEX(A2:A9, MATCH("Cherry", B2:B9, 0))

4. What happens if you leave out the last argument

XLOOKUP looks for an exact match unless you ask for something else. VLOOKUP and MATCH do the opposite: without their last argument, they assume the data is sorted and return the nearest value.

Our product column isn’t sorted. =VLOOKUP("Cherry", B2:E9, 3) returned 9 instead of 3, with no error; TRUE as the last argument gave the same 9. We saw the same thing with MATCH in INDEX MATCH not working. Always end VLOOKUP with FALSE and MATCH with 0.

5. When the value isn’t there

There is no Fig in the table. Both functions return #N/A with nearly the same message:

  • =VLOOKUP("Fig", B2:E9, 3, FALSE): Did not find value ‘Fig’ in VLOOKUP evaluation.
  • =XLOOKUP("Fig", B2:B9, D2:D9): Did not find value ‘Fig’ in XLOOKUP evaluation.

XLOOKUP has a fourth argument for your own text, such as XLOOKUP("Fig", B2:B9, D2:D9, "Not found"). With VLOOKUP and INDEX MATCH, wrap the formula in IFNA. See how to replace #N/A with a blank for why IFNA is safer than IFERROR.

A column number beyond the range gives a different error: =VLOOKUP("Cherry", B2:E9, 5, FALSE) returned #REF!, VLOOKUP evaluates to an out of bounds range. The range B:E has only 4 columns.

6. First or last match

Apple appears four times. =VLOOKUP("Apple", B2:E9, 3, FALSE) returned 10, from the first Apple row; INDEX MATCH also stops at the first match. Only XLOOKUP can search from the bottom up, with a search mode of -1:

=XLOOKUP("Apple", B2:B9, D2:D9, , 0, -1)

This returned 9, from row 9, the last Apple. That’s useful for “the most recent entry” in a log that grows downwards.

7. Several conditions

VLOOKUP searches one column, so it can’t match on two. XLOOKUP and INDEX MATCH can, with the conditions multiplied into a list of 1s and 0s, and both returned 10 for North and Apple. The difference surprised us:

  • =INDEX(D2:D9, MATCH(1, (A2:A9="North")*(B2:B9="Apple"), 0)) worked as it is.
  • =XLOOKUP(1, (A2:A9="North")*(B2:B9="Apple"), D2:D9) failed in every cell we tried, with #VALUE! or Array arguments to XLOOKUP are of different size. It worked once wrapped in ARRAYFORMULA.

The details, plus OR conditions and the last match, are in XLOOKUP with multiple criteria. To get every matching row, FILTER is simpler than any of the three.

8. Several result columns at once

=XLOOKUP("Cherry", B2:B9, C2:E9) returned Feb, 3, 3 in three cells. VLOOKUP needs a list of column numbers and ARRAYFORMULA: =ARRAYFORMULA(VLOOKUP("Cherry", B2:E9, {2, 3, 4}, FALSE)) returned the same three values, but without ARRAYFORMULA it returned only Feb, with no error. INDEX MATCH returns one column per formula in its usual form. More in XLOOKUP returning multiple columns.

9. Nearest value: grades from scores

Looking up the nearest value is how you turn a score into a grade. We used two copies of the same grade table, one sorted by minimum score (L2:M6) and one not (O2:P6), and looked up a score of 73, which should give a B (70 or more).

L: Min score (sorted) M: Grade O: Min score (unsorted) P: Grade
0 F 70 B
50 D 0 F
60 C 80 A
70 B 50 D
80 A 60 C
Formula Sorted table Unsorted table
=VLOOKUP(73, L2:M6, 2, TRUE) B F (with O2:P6)
=XLOOKUP(73, L2:L6, M2:M6, , -1) B B (with O and P)
=INDEX(M2:M6, MATCH(73, L2:L6, 1)) B F (with O and P)

The match mode -1 in XLOOKUP means “exact, or the next smaller value”, and it found the right grade even in the unsorted table. VLOOKUP and MATCH need sorted data and returned a wrong grade without an error. Match mode 1 goes the other way: =XLOOKUP(45, L2:L6, M2:M6, , 1) returned D, the grade for the next larger minimum, 50.

One more thing we didn’t expect: with a score of -5, lower than every minimum, =XLOOKUP(-5, L2:L6, M2:M6, , -1) returned an empty cell, not #N/A. ISBLANK on the result returned TRUE, ISNA returned FALSE, and IFNA left it empty. Only XLOOKUP’s own fourth argument replaced it: =XLOOKUP(-5, L2:L6, M2:M6, "none", -1) returned “none”.

Found in testing: XLOOKUP’s binary search modes (2 and -2) are not a speed setting you can switch on safely. On our unsorted product column, XLOOKUP("Cherry", B2:B9, D2:D9, , 0, 2) returned an empty cell instead of 3, and Banana came back empty too. Leave the search mode at its default unless the column is sorted.

10. Wildcards

VLOOKUP and MATCH treat * and ? as wildcards in exact mode: =VLOOKUP("Ch*", B2:E9, 3, FALSE) returned 3. XLOOKUP doesn’t, unless you ask: =XLOOKUP("Ch*", B2:B9, D2:D9) returned #N/A, Did not find value ‘Ch*’ in XLOOKUP evaluation., and =XLOOKUP("Ch*", B2:B9, D2:D9, , 2) returned 3. The XLOOKUP default is the safer one if your data contains real asterisks.

Things all three share

  • Case doesn’t matter. VLOOKUP("cherry", …) found “Cherry”, and so do XLOOKUP and MATCH.
  • The ranges must line up. A result range one row shorter than the lookup range gave XLOOKUP the error Array arguments to XLOOKUP are of different size. In INDEX MATCH, ranges that start on different rows return a value from the wrong row instead.
  • Extra spaces and numbers stored as text break all of them. See removing extra spaces and converting text to numbers.

Related guides