VLOOKUP is the function that finally makes a spreadsheet feel powerful. Once you can pull a price, a VAT code or a customer name from a table automatically, you stop copying figures by hand and your books get far more reliable. In this guide I will explain VLOOKUP in plain English using examples a UK business will recognise, and I will show you the one argument almost everyone gets wrong. There is no jargon here, just a function that, once it clicks, you will use every week.
The reason VLOOKUP matters so much for bookkeeping is that copy-and-paste is where errors creep in. Every time you retype a price or a VAT rate by hand, there is a chance of a typo, and a wrong figure on an invoice is the kind of mistake that costs money or goodwill. A lookup removes that risk entirely: you maintain one master list, and everything else reads from it. Update the master, and every formula that points at it updates too.
What VLOOKUP actually does
VLOOKUP looks down the first column of a table for a value you give it, then returns something from a column to the right on the same row. The name stands for “vertical lookup”. The four arguments are: what you are looking for, where to look, which column to bring back, and whether you want an exact match. Picture a paper price list: you run your finger down the codes until you find a match, then read across to the price. VLOOKUP does exactly that, just instantly and without losing your place.
The full shape is =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup). That last argument should almost always be FALSE for an exact match. Leave it as TRUE and Excel guesses, which is how wrong prices end up on invoices.
A worked UK example: a price list
Say you have a product list and you want to type a code and have the price appear automatically.
| Code | Product | Unit price (£) | VAT rate |
|---|---|---|---|
| SKU-100 | Oak shelf | 45.00 | 20% |
| SKU-220 | Pine desk | 120.00 | 20% |
| SKU-330 | Children’s book | 8.99 | 0% |
If that table sits in A2:D4 and you type a code into cell F2, the price comes back with =VLOOKUP(F2,A2:D4,3,FALSE). The 3 means “bring back the third column”, which is the unit price. To fetch the VAT rate instead, change the 3 to a 4. The same lookup value can feed several formulas, so once you have typed a code you can pull the product name, price and VAT rate all at once across a row, each with its own column number.
This is the heart of a quoting or invoicing sheet. You type a product code, and the description, unit price and VAT rate all appear without you touching the master list. Multiply the price by a quantity, apply the VAT rate, and you have a line total that is correct by construction. The only thing you ever type is the code and the quantity, which is exactly the kind of small, controlled input that keeps mistakes out of your books.
Stopping the error when a code is not found
If someone types a code that does not exist, VLOOKUP returns #N/A, which looks alarming on an invoice. Wrap it so it shows something friendlier: =IFERROR(VLOOKUP(F2,A2:D4,3,FALSE),"Code not found"). The IFERROR catches the problem and prints your own message instead.
Locking the table so it copies cleanly
When you copy a VLOOKUP down a column, the table reference shifts and breaks. Fix it in place with dollar signs: =VLOOKUP(F2,$A$2:$D$4,3,FALSE). The $ signs anchor the table so only the lookup cell moves. This single habit saves an enormous amount of frustration. A quick shortcut: click on the table reference in the formula bar and press F4, and Excel adds the dollar signs for you.
An even cleaner approach is to name your table. Select the master list, type a name such as PriceList into the Name Box to the left of the formula bar, and you can then write =VLOOKUP(F2,PriceList,3,FALSE). Named ranges never shift when copied and they read like plain English, so your formulas become self-explanatory. If you later add rows to the master list, you can expand the named range once and every formula benefits.
A second example: matching customer details
VLOOKUP is just as handy for pulling a customer’s email or terms from a master list when you only have their name. The principle is identical: name in the first column, the detail you want to the right, and FALSE for an exact match. If you keep a sales ledger, this is how you stop retyping the same customer information. Our invoice tracker template uses lookups in exactly this way.
One word of warning with names: people are inconsistent. “Acme Ltd”, “Acme Limited” and “ACME” will all be treated as different by VLOOKUP, so a code is always safer than a name as your lookup key. If you must look up by name, agree a single standard spelling for each customer and stick to it religiously. Better still, give every customer a short reference like CUST-001 and use that everywhere; it never changes and never has a typo lurking in it.
When you are ready to go further, look at XLOOKUP and at INDEX with MATCH. Both can do everything VLOOKUP does and more, including looking to the left and handling columns that get inserted without breaking. But do not feel you must rush there. VLOOKUP with FALSE and anchored references will carry you a very long way, and it is supported everywhere, from old copies of Excel to Google Sheets, which is why it remains the workhorse of business spreadsheets.
Common mistakes
- Leaving the last argument blank. Always add
FALSEfor an exact match unless you genuinely want the nearest-below value. - The lookup value is not in the first column. VLOOKUP only searches the leftmost column of your table. If your code sits in the middle, rearrange the table or use INDEX and MATCH instead.
- Forgetting to anchor the table. Without
$signs, copying the formula slides the table off the data. - Counting columns from the wrong place. The column index counts from the first column of your table, not from column A of the sheet.
Frequently asked questions
What does the FALSE at the end of VLOOKUP mean?
It tells Excel you want an exact match. TRUE (or a blank) asks for an approximate match, which only suits sorted numeric ranges like tax bands and is the wrong choice for codes and names.
Why does my VLOOKUP return #N/A?
It means the value was not found. Check for stray spaces, a code typed slightly differently, or numbers stored as text. Wrap the formula in =IFERROR(...) to show a tidy message.
Should I use VLOOKUP or XLOOKUP?
If you have a recent version of Excel, XLOOKUP is more flexible and can look left as well as right. VLOOKUP is still everywhere and works in older versions and Google Sheets, so it is well worth knowing.
Can VLOOKUP look to the left?
No. It only returns columns to the right of the lookup column. To look left, use INDEX and MATCH together, or switch to XLOOKUP.
