DEV Community

Cover image for XLOOKUP vs VLOOKUP in Excel: Differences and Formulas With Examples
epifive
epifive

Posted on Originally published at epifive.com

XLOOKUP vs VLOOKUP in Excel: Differences and Formulas With Examples

#certification #microsoft #tutorial

XLOOKUP is the better choice in Excel for Microsoft 365, Excel 2021 and Excel 2024: it uses exact match by default, can return values from columns to the left, and has a built-in "not found" message. VLOOKUP still matters when a workbook must open in Excel 2019 or older, because Microsoft states that XLOOKUP is not available in Excel 2016 and Excel 2019.

This guide puts both functions side by side on one sample table, gives every formula in copy-paste form, and shows the result each formula returns so you can check your own sheet against it.

Who this is for and what you will have at the end

This is for anyone who builds price lists, inventory sheets, staff lists or reports in Excel and wants to know which lookup function to use. It suits beginners who have typed a VLOOKUP before and want to understand XLOOKUP, and people who keep getting #N/A or wrong values from an existing formula.

By the end you will be able to:

  • Write the same lookup with VLOOKUP, XLOOKUP and INDEX with MATCH.
  • Look up to the left, return a friendly message when nothing matches, and match the next lower tier in a rate table.
  • Look up on two conditions at once, return several columns in one formula, and use wildcards.
  • Recognize and fix #N/A, #REF!, #VALUE!, #SPILL! and the silent wrong-column problem.

Prerequisites

  • Excel for Microsoft 365, Excel 2024 or Excel 2021 for the XLOOKUP examples. Microsoft's XLOOKUP page also lists Excel for iPad, iPhone and Android.
  • Any recent desktop version for the VLOOKUP and INDEX with MATCH examples. Microsoft's VLOOKUP page lists Excel 2016 through Excel for Microsoft 365.
  • The sample table below typed into a blank sheet, starting in cell A1.

The sample table

Every formula in this article uses this product list. Type it into A1:F10. Note that two products appear twice with different sizes, which matters later.

Row A: Product ID B: Product C: Size D: Category E: Price F: Stock
1 Product ID Product Size Category Price Stock
2 C-101 Contoso Desk Lamp Small Lighting 24.99 120
3 C-102 Contoso Desk Lamp Large Lighting 39.99 45
4 C-103 Fabrikam Office Chair Standard Furniture 149.00 18
5 C-104 Fabrikam Office Chair Executive Furniture 289.00 6
6 C-105 Northwind Coffee Mug Small Kitchen 8.50 300
7 C-106 Northwind Coffee Mug Large Kitchen 11.00 210
8 C-107 Contoso Monitor Stand Standard Accessories 34.00 75
9 C-108 Tailspin Wireless Mouse Standard Accessories 19.99 0
10 C-109 Tailspin USB-C Hub Standard Accessories 45.00 60

For the approximate-match section you also need a small volume discount table in J1:K5. Format column K as a percentage.

Row J: Min quantity K: Discount
1 Min quantity Discount
2 1 0%
3 10 5%
4 50 10%
5 100 15%

XLOOKUP vs VLOOKUP at a glance

VLOOKUP XLOOKUP
Syntax VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Default match Approximate (range_lookup TRUE) Exact (match_mode 0)
Return column Column number counted from the left of table_array A separate range you point to
Look to the left No Yes
Not-found message Wrap in IFNA or IFERROR if_not_found argument
Search from the bottom No search_mode -1
Return several columns One column per formula Yes, results spill
Available in Excel 2016 and 2019 Yes No

Microsoft's own Excel blog post announcing XLOOKUP lists the VLOOKUP limitations it was built to remove: approximate match by default, a column number that has to be updated when you insert or delete a column, no way to look left, and no way to search from the end. The same post says VLOOKUP and HLOOKUP will continue to be supported.

Step 1: Exact match with VLOOKUP

Find the price of product C-104. VLOOKUP searches the first column of table_array and returns the value from the column number you give. Price is the fifth column of A2:F10, so col_index_num is 5.

=VLOOKUP("C-104",$A$2:$F$10,5,FALSE)
Enter fullscreen mode Exit fullscreen mode

Result: 289.

Two details matter here. First, the last argument is FALSE. Microsoft's VLOOKUP page says approximate match (TRUE) is the default if you leave range_lookup out, and approximate match assumes the first column is sorted. Leave it out on an unsorted list and you can get a wrong price with no error. Second, the range uses absolute references ($A$2:$F$10), which Microsoft recommends so the range does not shift when you fill the formula down.

In real sheets you will usually point at a cell instead of typing the ID. If H2 contains C-104, this returns the same result:

=VLOOKUP(H2,$A$2:$F$10,5,FALSE)
Enter fullscreen mode Exit fullscreen mode

Step 2: The same lookup with XLOOKUP

XLOOKUP takes the column to search and the column to return as two separate ranges.

=XLOOKUP("C-104",A2:A10,E2:E10)
Enter fullscreen mode Exit fullscreen mode

Result: 289.

There is no column number to count and no FALSE to remember. Microsoft's XLOOKUP page lists match_mode 0, exact match, as the default, and the function returns the item for the first match it finds.

The INDEX and MATCH equivalent, which also works in Excel 2016 and 2019, uses MATCH with match_type 0 for an exact match:

=INDEX(E2:E10,MATCH("C-104",A2:A10,0))
Enter fullscreen mode Exit fullscreen mode

Result: 289.

Step 3: Look up to the left

Suppose you know the product name and need its ID. The ID is in column A, to the left of the Product column, so VLOOKUP cannot do it: Microsoft's lookup guide says VLOOKUP can only look up from left to right. XLOOKUP does not care about direction.

=XLOOKUP("Tailspin USB-C Hub",B2:B10,A2:A10)
Enter fullscreen mode Exit fullscreen mode

Result: C-109.

With INDEX and MATCH:

=INDEX(A2:A10,MATCH("Tailspin USB-C Hub",B2:B10,0))
Enter fullscreen mode Exit fullscreen mode

Result: C-109.

Step 4: Show a message instead of #N/A

When nothing matches, both functions return #N/A. XLOOKUP's fourth argument, if_not_found, replaces that with your own text.

=XLOOKUP("C-200",A2:A10,E2:E10,"Not found")
Enter fullscreen mode Exit fullscreen mode

Result: Not found (there is no C-200 in the list).

VLOOKUP has no such argument, so wrap it in IFNA, which returns the value you specify only when the formula evaluates to #N/A:

=IFNA(VLOOKUP("C-200",$A$2:$F$10,5,FALSE),"Not found")
Enter fullscreen mode Exit fullscreen mode

Result: Not found.

IFERROR also works, but it hides every error, including a #REF! from a broken range. IFNA only catches the not-found case, so real mistakes stay visible.

Step 5: First match or last match

The product "Contoso Desk Lamp" appears in rows 2 and 3. A plain lookup returns the first one from the top.

=XLOOKUP("Contoso Desk Lamp",B2:B10,E2:E10)
Enter fullscreen mode Exit fullscreen mode

Result: 24.99 (the Small lamp in row 2).

Set search_mode to -1 to search from the last item upward. This is useful for logs where the newest entry is at the bottom.

=XLOOKUP("Contoso Desk Lamp",B2:B10,E2:E10,"Not found",0,-1)
Enter fullscreen mode Exit fullscreen mode

Result: 39.99 (the Large lamp in row 3). VLOOKUP has no option to search from the end.

Step 6: Approximate match for tiers and rate tables

Tiered prices, discounts, commissions and tax brackets need the "next smaller" value: an order of 64 units falls into the 50-and-up tier. Use the discount table in J1:K5.

With XLOOKUP, set match_mode to -1, which Microsoft describes as an exact match that returns the next smaller item when none is found:

=XLOOKUP(64,J2:J5,K2:K5,"No tier",-1)
Enter fullscreen mode Exit fullscreen mode

Result: 10%.

The same lookup with VLOOKUP and range_lookup set to TRUE:

=VLOOKUP(64,$J$2:$K$5,2,TRUE)
Enter fullscreen mode Exit fullscreen mode

Result: 10%.

More results from the same table, so you can test your sheet:

Quantity XLOOKUP with match_mode -1 VLOOKUP with TRUE
9 0% 0%
64 10% 10%
100 15% (exact match) 15%
250 15% 15%
0 No tier #N/A

The difference is in the details. Microsoft's VLOOKUP page says approximate match assumes the first column is sorted, and that a lookup value smaller than the smallest value in the first column returns #N/A. XLOOKUP's page lists a sorting requirement only for the binary search modes, search_mode 2 and -2, and not for match_mode -1, and with if_not_found you get your own text instead of #N/A. Keep tier tables sorted ascending anyway, because sorted tiers are easier to read and check.

If you need the next larger value instead, for example the smallest box size that fits an item, use match_mode 1.

Step 7: XLOOKUP with two criteria

The product name alone is ambiguous: there are two Northwind Coffee Mugs. To get the price of the Large one, match on Product and Size together.

=XLOOKUP(1,(B2:B10="Northwind Coffee Mug")*(C2:C10="Large"),E2:E10,"Not found")
Enter fullscreen mode Exit fullscreen mode

Result: 11.

How it works: each comparison in brackets returns an array of TRUE and FALSE values, one per row. Multiplying them turns TRUE into 1 and FALSE into 0, so a row ends up as 1 only when both conditions are true. Microsoft's FILTER function page uses the same multiplication pattern for "and" conditions, and its array formula guidelines explain that TRUE*1=1 and FALSE*1=0. For this table the multiplied array is 0,0,0,0,0,1,0,0,0, and XLOOKUP finds the 1 in the sixth position, which is row 7.

To use cell inputs, put the product in H2 and the size in I2:

=XLOOKUP(1,($B$2:$B$10=H2)*($C$2:$C$10=I2),$E$2:$E$10,"Not found")
Enter fullscreen mode Exit fullscreen mode

Add a third condition by multiplying another bracket, for example *(D2:D10="Kitchen").

Two criteria in Excel 2019 and older

The INDEX and MATCH version works the same way:

=INDEX(E2:E10,MATCH(1,(B2:B10="Northwind Coffee Mug")*(C2:C10="Large"),0))
Enter fullscreen mode Exit fullscreen mode

Result: 11. This is an array formula. Microsoft's guidance is to press Enter in Excel for Microsoft 365, and Ctrl+Shift+Enter in older versions.

VLOOKUP with multiple criteria

VLOOKUP takes one lookup_value and only searches the first column of table_array, so it needs a helper column that joins the conditions, placed at the left edge of the table. On a copy of the sheet, insert a new column before column A. The table now runs A:G, with Price in column F. In A2 enter the formula below and fill it down to A10:

=C2&"|"&D2
Enter fullscreen mode Exit fullscreen mode

Then look up the joined key:

=VLOOKUP("Northwind Coffee Mug|Large",$A$2:$G$10,6,FALSE)
Enter fullscreen mode Exit fullscreen mode

Result: 11. The pipe character keeps the two values separate so that different combinations cannot run together into the same key.

Step 8: Return several columns at once

XLOOKUP can return more than one column. Point return_array at several adjacent columns and the results spill into the cells to the right.

=XLOOKUP("C-103",A2:A10,D2:F10)
Enter fullscreen mode Exit fullscreen mode

Result: three cells, Furniture, 149 and 18 (Category, Price and Stock for the Standard office chair).

Microsoft's XLOOKUP page gives the same pattern, returning an employee's name and department from one formula. The cells to the right must be empty, or the formula returns #SPILL! (see the errors section). With VLOOKUP you would write three formulas with col_index_num 4, 5 and 6.

Step 9: Wildcard lookups

In XLOOKUP, wildcards need match_mode 2, which Microsoft describes as a wildcard match where *, ? and ~ have special meaning. Microsoft's wildcard page defines them: ? matches any single character, * matches any number of characters, and ~ before ?, * or ~ finds that character itself.

=XLOOKUP("*Chair*",B2:B10,A2:A10,"Not found",2)
Enter fullscreen mode Exit fullscreen mode

Result: C-103 (the first product whose name contains "Chair").

=XLOOKUP("*Mug",B2:B10,A2:A10,"Not found",2)
Enter fullscreen mode Exit fullscreen mode

Result: C-105.

VLOOKUP supports the same wildcards when range_lookup is FALSE and the lookup value is text. Searching column B means table_array starts at B, so Price is now the fourth column:

=VLOOKUP("Tailspin*",$B$2:$F$10,4,FALSE)
Enter fullscreen mode Exit fullscreen mode

Result: 19.99 (Tailspin Wireless Mouse, the first match).

When you still need VLOOKUP

XLOOKUP is the better default, but VLOOKUP or INDEX with MATCH is still the right call in these cases:

  • Colleagues or customers use Excel 2016 or 2019. Microsoft's XLOOKUP page states it is not available in those versions. If you cannot confirm what version the recipient has, stay with functions their version supports.
  • You are maintaining an existing workbook. Replacing working VLOOKUPs adds risk without changing the result. Fix the range_lookup and absolute-reference problems first.
  • You need the formula to work everywhere and also look left or on two criteria. Use INDEX with MATCH, which handles both and works in Excel 2016 and 2019.

Common errors and fixes

Error Typical cause Fix
#N/A The value is not in the lookup column, has extra spaces, or is a number stored as text on one side Check the value exists, clean spaces with TRIM, make both sides the same data type, or add if_not_found or IFNA
#N/A with approximate match Lookup value is smaller than the first value in a sorted tier table Add a lowest tier (such as 1 or 0) at the top of the table
Wrong value, no error VLOOKUP with range_lookup left out on an unsorted list Add FALSE as the fourth argument
#REF! col_index_num is larger than the number of columns in table_array Widen table_array or lower col_index_num
#VALUE! col_index_num is less than 1 or contains text, or the lookup value is longer than 255 characters Use a column number of 1 or more; for long values use INDEX and MATCH
#NAME? A text lookup value is typed without quotes Write "C-104" with quotes, or point at a cell
#SPILL! Cells in the spill range are not empty, the formula is inside an Excel table, or a whole-column lookup runs past the sheet edge Clear the blocking cells, move the formula outside the table, or look up a specific range such as A2:A10

The column-insert problem

This one does not produce an error, which is why it is dangerous. Start with the VLOOKUP from Step 1, which returns 289. Now insert a new column between Product and Size, for example for Color. Excel widens the range to $A$2:$G$10, but the number 5 stays the same. Column 5 is now Category, so the formula returns Furniture instead of a price.

The fix is either to update col_index_num by hand every time the layout changes, which Microsoft's XLOOKUP announcement calls out as a VLOOKUP limitation, or to switch to XLOOKUP or INDEX with MATCH. Those point at the Price column as a range, so the reference moves with the column when you insert one.

#N/A from hidden spaces and numbers stored as text

Microsoft's #N/A troubleshooting page names mismatched data types as a common cause, such as a number in the lookup cell and text in the source column. Check with Format Cells (Ctrl+1), and remove leading or trailing spaces with TRIM. For larger cleanups, see cleaning messy spreadsheet data in Excel.

#SPILL! when returning several columns

Microsoft's #SPILL! page lists the usual causes: the spill range is not blank, the formula is in an Excel table (spilled array formulas are not supported there), or the result extends beyond the edge of the worksheet. Select the formula cell to see a dashed border around the intended spill range, then clear whatever is inside it. If the formula sits in a table, move it out of the table or convert the table to a range from Table Design.

Verify your formulas

Before relying on a lookup in a report, test it against known answers:

  1. Pick one ID you can see in the table and confirm the lookup returns the same value you read off the row.
  2. Type an ID that does not exist and confirm you get your "Not found" text, not a stray value.
  3. For tier tables, test a value below the first tier, one exactly on a boundary (such as 100) and one above the last tier.
  4. For two-criteria lookups, test a combination that does not exist, such as "Fabrikam Office Chair" with "Small", and confirm you get "Not found".
  5. Insert a test column inside the source range and check every lookup still returns the right field, then undo.

Checklist

  • Use XLOOKUP when everyone who opens the file has Excel for Microsoft 365, Excel 2021 or Excel 2024.
  • Use VLOOKUP or INDEX with MATCH when Excel 2016 or 2019 users must open the file.
  • Always end a VLOOKUP exact match with FALSE.
  • Lock ranges with $ before you fill formulas down.
  • Give every lookup a not-found result with if_not_found or IFNA.
  • Keep approximate-match tables sorted ascending with a lowest tier at the top.
  • For two conditions, use 1 as the lookup value and multiply the comparisons.
  • Leave empty cells to the right of any formula that returns several columns.
  • Re-check VLOOKUP column numbers after inserting or deleting columns.

More Microsoft 365 tutorials

References

This guide was researched and drafted with AI assistance and checked against the vendor documentation listed in its References. Read the maintained version and related guides on EpiFive.

Top comments (0)