VLOOKUP
Look a value up in the first column of a block.
Read a value off a reference table by matching on its first column
The value in the column you asked for, from the row that matched
Syntax
=VLOOKUP(lookup_value, table, column_number, [exact_match])
| Argument | Required | What goes here |
|---|---|---|
lookup_value | Required | The value to find, like E2. |
table | Required | The block to search. Its first column is the one searched. |
column_number | Required | Which column of that block to read back, counting the first as 1. |
exact_match | Optional | FALSE for an exact match, which is almost always what you want. |
How to use VLOOKUP
VLOOKUP takes what to look for, the block to look in, which column of that block to read, and whether the match has to be exact.
The last argument is the one that matters most. FALSE means exact, and on a ledger the answer is always FALSE. Leaving it out means approximate, which quietly returns the nearest row below and looks like a real answer.
The column number counts from the left edge of the block, not from column A of the sheet. Insert a column inside the block and every formula pointing at it is now reading the wrong one, with no error to say so.
The value you match on has to be in the first column of the block. That is the limitation people hit, and INDEX with MATCH or XLOOKUP is the way around it.
Anchor the block with dollar signs before copying the formula down, or the range slides a row at a time and the bottom of the column stops finding anything.
Examples
Every result below is worked out by the same evaluator that marks your answers, on the sheet shown here.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Invoice | Supplier | Status | Days overdue | Amount | Supplier | Terms (days) | |
| 2 | INV-8801 | Brightwell Paper | Overdue | 12 | 940 | Ashgrove Catering | 30 | |
| 3 | INV-8802 | Calder Utilities | Queried | 47 | 1180 | Brightwell Paper | 30 | |
| 4 | INV-8803 | Meridian Freight | Overdue | 63 | 2450 | Calder Utilities | 14 | |
| 5 | INV-8804 | Ashgrove Catering | On terms | 0 | 405 | Meridian Freight | 45 | |
| 6 | INV-8805 | Brightwell Paper | Overdue | 38 | 760 | Thornbury Print | 60 | |
| 7 | INV-8806 | Thornbury Print | Overdue | 91 | 1320 | |||
| 8 | INV-8807 | Meridian Freight | On terms | 0 | 580 | |||
| 9 | INV-8808 | Calder Utilities | Overdue | 24 | 215 |
| Formula | Result |
|---|---|
=VLOOKUP(B2,G2:H6,2,FALSE)Brightwell Paper's agreed terms, read off the table on the right. | 30 |
=VLOOKUP(B7,$G$2:$H$6,2,FALSE)The same formula with the block anchored, which is how it survives being filled down a column. | 60 |
=D2-VLOOKUP(B2,G2:H6,2,FALSE)Days past the agreed terms, which needs the lookup before it can be worked out. Negative means the invoice is still inside its terms. | -18 |
=IFERROR(VLOOKUP("Harbour Ltd",G2:H6,2,FALSE),30)A supplier that is not on the table. The fallback is a decision about what a missing supplier means, and should only be written once the lookup has been proved on a row that works. | 30 |
Notes
- Always write FALSE as the fourth argument. Approximate matching on a ledger returns a wrong answer rather than an error.
- The column number is counted inside the block, so inserting a column breaks every formula pointing at it and nothing warns you.
- VLOOKUP cannot read a column to the left of the one it matched on.
Related
A reference tells you what it does. A unit makes you use it.