Excel Functions: VLOOKUP and XLOOKUP
Lookup is a top Excel use case. VLOOKUP and XLOOKUP are the two workhorses.
VLOOKUP Basics
=VLOOKUP(value, range, col_index, FALSE) searches the first column of a range and returns a value from the given column.
VLOOKUP Limits
- Only looks right
- Column referenced by number — breaks when columns are added
- Returns #N/A when not found
XLOOKUP
=XLOOKUP(value, lookup_col, return_col, not_found) fixes all of the above and works both directions.
Comparison
| Function | Direction | Column ref | Not found |
|---|---|---|---|
| VLOOKUP | Right only | Number | #N/A |
| XLOOKUP | Both | Whole col | Customizable |
Tools
Convert Excel data to JSON for programs? Use the online tool to do it in one click.