The two most requested formulas in any office. Understand the difference and never look them up again.
VLOOKUP searches a table for a value in the leftmost column and returns a value from the same row, a specified number of columns to the right. It's been the go-to lookup formula for 30 years.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
-- Example: Find the price for product code "A103"
=VLOOKUP("A103", A2:D100, 3, FALSE)
-- Returns the value in column 3 (price) of the row where column 1 = "A103"VLOOKUP can only look left-to-right. Your lookup value must be in the leftmost column of your range. If you need to look right-to-left (e.g., find a name from an ID in column C when names are in column A), VLOOKUP breaks. That's where XLOOKUP comes in.
XLOOKUP separates the 'where to search' from 'what to return', so you can look in any direction. It also handles not-found errors natively with the if_not_found argument.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
-- Same example: Find the price for product code "A103"
=XLOOKUP("A103", A2:A100, C2:C100, "Not found")
-- lookup_array: just column A (product codes)
-- return_array: just column C (prices)
-- No need to count columns!Use VLOOKUP if you're sharing files with people on older Excel versions (2016 or earlier) — XLOOKUP wasn't added until 2019. Otherwise, use XLOOKUP every time. It's more readable, handles errors better, and doesn't break when you insert columns.