Excel like a finance pro.
The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.
VLOOKUP
Lookups & referencesThe everyday lookup — find a value in the first column and return one to its right.
Difficulty
1What is it?
VLOOKUP searches down the first column of a table for a value and returns a cell from a column to the right, on the same row. It's still the most-used lookup in the world. Always end with FALSE for an exact match — and know its limits: it only looks rightward, and the column number is hard-coded, so inserting a column quietly breaks it.
2What it looks like
VLOOKUP(lookup, table_array, col_index_num, [range_lookup])- lookup
- The value to find — must be in the first column of the table.
- table_array
- The table to search; the lookup column has to be the leftmost one.
- col_index_num
- Which column to return, counted from the left of the table (1 = first).
- [range_lookup]
- FALSE for an exact match (almost always); TRUE assumes a sorted list and finds the closest.
3When you use it
- Map a code or SKU to a name or price stored to its right.
- Pull a rate, tier, or owner onto a row by key.
- Quick join between two tables that share a key column.
4See it in action
Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.
Find a code in the first column, return the name from column 2.
=VLOOKUP("4100", A2:B4, 2, FALSE)Service revenue| A | B | C | |
|---|---|---|---|
| 1 | Code | Name | Result |
| 2 | 4000 | Product revenue | Service revenue |
| 3 | 4100 | Service revenue | |
| 4 | 5000 | Cost of goods sold |
The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.
5Common errors
#N/ANo exact match — or you left off FALSE.Fix: Add FALSE for an exact match, TRIM stray spaces, and confirm the key sits in the first column.
#REF!col_index_num is wider than the table.Fix: Count columns from the left of table_array, not from the sheet.
Wrong valuerange_lookup was TRUE on unsorted data.Fix: Use FALSE for exact matches.
Breaks on insertA column was inserted, shifting the count.Fix: Switch to XLOOKUP or INDEX/MATCH, which don't hard-code a column number.
6Better functions & alternatives
- XLOOKUP — Looks any direction, exact by default, with a built-in not-found value. Prefer it when available.
- INDEX + MATCH — Looks left too and survives inserted columns.
- HLOOKUP — The horizontal sibling — searches across the top row instead of down the first column.
Want VLOOKUP already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.
Learn the moves here — or let Wauvel run them on your numbers.
Get my free report →