XLOOKUP vs VLOOKUP in Excel and Google Sheets (+ 5 Daily Formulas)

A lookup formula finds a value in one column and returns a related value from the same row, for example a price for a product ID. The syntax below comes from Microsoft's and Google's own help pages, checked on October 29, 2026.

The Example Table

All examples use this small table in cells A1 to C5. The data is made up for practice.

• Row 1 (headings): ID in A1, Product in B1, Price in C1
• Row 2: P101, Pen, 1.20
• Row 3: P102, Notebook, 3.50
• Row 4: P103, Stapler, 7.90
• Row 5: P104, Tape, 2.10

Type an ID, for example P102, in cell E2. We want the price in F2.

VLOOKUP: The Classic Way

In Excel the syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Google Sheets uses the same idea with VLOOKUP(search_key, range, index, is_sorted).

Put this in F2:

=VLOOKUP(E2, A2:C5, 3, FALSE)

• E2 is the value to find (P102)
• A2:C5 is the table. The ID must be in its first column
• 3 means "return the value from the third column of the table", which is Price
• FALSE asks for an exact match

Limits of VLOOKUP: the value you search for must be in the first column and the answer must be to its right. The column number is typed by hand, so inserting a column can break the result, and a value that is not found returns #N/A.

XLOOKUP: The Newer Way

In Excel the syntax is XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). Google Sheets uses XLOOKUP(search_key, lookup_range, result_range, missing_value, match_mode, search_mode).

Put this in F2:

=XLOOKUP(E2, A2:A5, C2:C5, "Not found")

• A2:A5 is the column to search
• C2:C5 is the column to return, so you point at the answer directly and there is no column number
• "Not found" is shown if there is no match, instead of #N/A

XLOOKUP uses an exact match by default, so you do not need to add FALSE.

Look-left example: if your table has Email in column B and Name in column A, =XLOOKUP(E2, B2:B20, A2:A20) finds a name from an email. VLOOKUP cannot do this directly.

VLOOKUP vs XLOOKUP: Which to Use?

• Use XLOOKUP if you work in Google Sheets or in a recent Excel and want simpler, safer formulas
• Use VLOOKUP if the file must open in Excel 2016 or 2019, or if you share it with people who have older versions
• Mixed team? Check the version first. If in doubt, VLOOKUP with FALSE works everywhere

5 Formulas People Use Every Day

These examples use a second made-up table: customer names in column A, order totals in column B and status ("Paid" or "Pending") in column C, rows 2 to 20.

1. IF: show a different result

=IF(B2>=100, "Big order", "Small order")

If the total in B2 is 100 or more, the cell shows "Big order". Otherwise it shows "Small order".

2. SUMIFS: add numbers that match conditions

=SUMIFS(B2:B20, A2:A20, "Anna", C2:C20, "Paid")

This adds all totals for the customer Anna whose status is Paid. The first range is what you add, then pairs of range and condition.

3. COUNTIF: count matching cells

=COUNTIF(C2:C20, "Paid")

This counts how many orders have the status Paid.

4. IFERROR: hide ugly errors

=IFERROR(B2/D2, 0)

If the division fails (for example D2 is zero), the cell shows 0 instead of an error. Google Sheets also has IFNA, which only catches #N/A errors, useful around VLOOKUP.

5. TRIM: clean extra spaces

=TRIM(A2)

TRIM removes extra spaces at the start and end of text. Hidden spaces are a common reason a lookup does not find a match.

Why Your Lookup Shows #N/A

1. The value really is not in the table. Check the spelling and the ID
2. Extra spaces. Clean the data with TRIM, then try again
3. Numbers stored as text. A number typed as text will not match a real number, so make both the same type
4. Approximate match. In VLOOKUP, add FALSE as the last argument
5. Wrong range. In VLOOKUP, the search value must be in the first column of the table you selected

Try It Yourself

1. Type the example table into a new sheet
2. Write the VLOOKUP formula in F2 and test it with P101 and P104
3. Write the XLOOKUP formula in G2 and compare the result
4. Type a wrong ID and see what each formula returns
5. Add a column on the left and see which formula keeps working

If your spreadsheet shows an error such as "Formula parse error", your region may use semicolons instead of commas between arguments. Try ; in place of ,.

Frequently Asked Questions