XLOOKUP vs VLOOKUP: Which Should You Use?

The short answer

Both functions find a value in a list and return something from the same row. The difference is how much can go wrong along the way. VLOOKUP needs a column number and an extra argument to avoid approximate matching. XLOOKUP asks for the column you search and the column you want back, and it defaults to an exact match.

XLOOKUP vs VLOOKUP side by side

What goes wrong with VLOOKUP

Take a product list in columns A to C (SKU, item, price). To find the price for the SKU in E2, the classic formula is:

=VLOOKUP(E2, A:C, 3, FALSE)

• The 3 is hard-coded. If someone inserts a column between A and C, the 3 now points at the wrong column and nothing warns you.
• It only looks right. The value you search for must be in the first column of the range. If the SKU is in column C and the price in A, you cannot use VLOOKUP without rearranging the sheet.
• The last argument is easy to forget. Leave out FALSE and Excel does an approximate match, which can quietly return the wrong row.

What XLOOKUP fixes

The same job in XLOOKUP:

=XLOOKUP(E2, A:A, C:C, "Not found")

You name the column to search (A:A) and the column to return (C:C), so inserting a column cannot shift what you get back, and the search column can sit anywhere. The fourth argument is what to show when nothing matches, so you no longer need to wrap the formula in IFERROR.

How to convert a VLOOKUP to XLOOKUP

1. Take the lookup value, the first argument of your VLOOKUP, and keep it as the first argument of XLOOKUP.
2. Split the VLOOKUP table into two columns: the first column becomes the search range and the column you want back becomes the return range.
3. Delete the column number and the TRUE or FALSE. Exact matching is the default.
4. Add a friendly message as the fourth argument if you want one.

When to still use VLOOKUP

• The workbook will be opened in Excel 2019, 2016 or earlier, which do not have XLOOKUP.
• You are maintaining an older workbook where VLOOKUP already works and rewriting it adds risk.
• You are preparing for an exam built on an older Office version. MO-201 is based on Office 2019, so it tests VLOOKUP, HLOOKUP, INDEX and MATCH rather than XLOOKUP.

Three XLOOKUP tricks worth learning

• Return several columns at once. Give XLOOKUP a return range of more than one column, such as C:D, and it spills both values into the cells next to the formula.
• Find the last match. Set the last argument (search_mode) to -1 to search from the bottom up, useful for the most recent entry.
• Match with wildcards. Set match_mode to 2 to use * and ? in the lookup value.

Once lookups feel natural, the next skills to learn are SUMIFS, PivotTables and Power Query. Our 30-day Excel plan shows the order we recommend.

Frequently asked questions

Sources and disclaimers

Function behaviour and availability are described in Microsoft’s documentation for XLOOKUP. Confirm availability for your version of Excel before sharing a workbook. ExcelPanda is not affiliated with or endorsed by Microsoft.