XLOOKUP vs VLOOKUP: Which to Use and How
TL;DR: XLOOKUP is the modern replacement for VLOOKUP: it can look left, defaults to an exact match and won't break when you insert columns. Use VLOOKUP only if you share files with people on old Excel versions. Google Sheets supports both.
VLOOKUP
=VLOOKUP(lookup_value, table, column_number, FALSE)Example: find the price of product code in E2 in a table A2:C100 where price is column 3:
=VLOOKUP(E2, A2:C100, 3, FALSE)Always use FALSE for an exact match. VLOOKUP can only look to the right of the first column, and inserting a column breaks the column number.
XLOOKUP
=XLOOKUP(lookup_value, lookup_range, return_range, [if_not_found])=XLOOKUP(E2, A2:A100, C2:C100, "Not found")XLOOKUP defaults to exact match, can return values to the left, survives inserted columns and has a built-in "not found" message. It is available in Excel 2021, Microsoft 365 and Google Sheets.
Which to choose
Use XLOOKUP if everyone who opens the file has a modern version. Use INDEX/MATCH or VLOOKUP for compatibility with Excel 2019 and earlier. Our INDEX MATCH guide covers left lookups, two-way tables and multiple criteria.