One searches down columns, one searches across rows — and in 2026 there's a better option for both.
VLOOKUP searches down the first column of a range for a value, then returns data from a column to the right.
=VLOOKUP(lookup_value, table_range, col_index, [match_type])
=VLOOKUP(A2, $D$2:$F$100, 2, 0)
-- Find A2 in column D, return the value from column E (2nd column of the range)The "V" stands for Vertical. Use VLOOKUP when your lookup table has categories or IDs running down the left column.
HLOOKUP searches across the first row of a range for a value, then returns data from a row below.
=HLOOKUP(lookup_value, table_range, row_index, [match_type])
=HLOOKUP("Q2", $A$1:$D$5, 3, 0)
-- Find "Q2" in row 1, return the value from row 3The "H" stands for Horizontal. Use HLOOKUP when your lookup table has headers across the top row. This is less common than VLOOKUP in practice.
XLOOKUP searches in any direction and handles both vertical and horizontal lookups with cleaner syntax.
=XLOOKUP(A2, D2:D100, E2:E100, "Not found")
-- Replaces VLOOKUP — no column index number, can look left
=XLOOKUP("Q2", A1:D1, A3:D3, "Not found")
-- Replaces HLOOKUP — same function, different range orientationIf you have Excel 365, use XLOOKUP for both vertical and horizontal lookups — it's simpler and more powerful. Only use VLOOKUP or HLOOKUP if you need the file to work in Excel 2019 or earlier.
ExcelPro has 880+ hands-on exercises across 9 tracks — type real formulas, get instant feedback.
Start practising free →