Why VLOOKUP isn't working and How to Use VLOOKUP Without Getting Errors Every Time

VLOOKUP is probably the most famous function in Excel. It’s also one of the most frustrating.

If you’ve ever typed a VLOOKUP formula, hit Enter, and been greeted by N/A, REF!, or just a completely wrong answer, you’re not alone.

It’s one of the most Googled Excel problems for a reason!

The thing is, VLOOKUP isn’t actually difficult once you understand how it works, but it isn't immediately obvious how to use it properly.

Most of the errors people encounter come from the same handful of mistakes – and they’re all fixable.

This post walks you through how VLOOKUP works, why it goes wrong, how to fix the most common errors, and a brilliant trick for making your formulas more user-friendly.

I’ll also let you in on something your current Excel course might not have told you: there’s a newer, better function that’s replaced VLOOKUP entirely.

Here's how to use VLOOKUP and make it work

VLOOKUP stands for “Vertical Lookup.” In plain English, it searches for a value in the first column of a table and returns a corresponding value from another column in the same row.

That’s it.

Imagine you’ve got a table of employee data like this:

Name	Performance Rating	Bonus
George Smith	5
Jack Hughes	5
Leo Edwards	5
Luca Moretti	5
Maya Kimura	2

We also have a second table which lists the Bonus amount for each Performance Rating.

Performance Rating	Bonus
5	£6,000
4	£4,000
3	£2,500
2	£1,000
1	£0

Here's what it might look like in Excel.

Note: the table_array doesn't have to be on exactly the same rows as your data like in the example - it's just easier to see this way.

We want to create a formula to look up each employee's Performance Rating and write in the correct Bonus amount.

The formula has four parts:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value – the value you’re searching for (this is usually just a cell reference).

table_array – the range of cells that contains both the column you want it to search in (such as Performance Rating) and the column you want it to return (such as Bonus).

col_index_num – which column number in your table_array you want the result from (counting from the left of your table array – so column 1 is the first column, column 2 is the second, and so on).

range_lookup – whether you want an exact match or an approximate match (more on this in a moment – this is where most errors come from).

To find George Smith's Bonus amount, you would write:

=VLOOKUP(B3,E3:F7,2,FALSE)

Let's break it down:

lookup_value B3: look for the contents of cell B3 (in this case the number 5).

table_array E3:F7: look in cells E3 to F7.

col_index_num 2: return the value from the second column in the table array (Bonus).

range_lookup FALSE: when you are writing the formula and get to this part, a dropdown menu will ask you whether you want an exact or approximate match. We want exact - the menu tells us to write FALSE.

The answer for George would be £6000.

Important Note:

If you are going to copy the formula down for all of the other employees you need to make sure the table_array uses absolute references.

This prevents the table_array from moving when we copy the formula.

If it did move, all of our other lookups would break as it would be looking at the wrong cells.

To make the cells reference absolute, just press F4 (on some keyboards you need to hold down the Function key - Fn - and then press F4.

=VLOOKUP(B3,$E$3:$F$7,2,FALSE)

For more detail on absolute references, check out this other post: Absolute vs Relative References.

Exact Match vs Approximate Match (The TRUE / FALSE Trap)

This is the single biggest source of VLOOKUP errors, and it all comes down to that last part of the formula: the range_lookup argument.

FALSE (or 0) = exact match. VLOOKUP will only return a result if it finds a value that matches your lookup value exactly. If it can’t find it, you’ll get an N/A error.

This is what you want the vast majority of the time – when you’re looking up employee names or IDs, product codes, invoice numbers, or anything else where you need a precise match.

TRUE (or 1) = approximate match. VLOOKUP will find the closest match that is less than or equal to your lookup value.

This is designed for situations where your data is in numerical ranges – for example, tax brackets, grading bands, or commission tiers where you need to find which band a number falls into.

Here’s the trap: if you leave the last argument out entirely, Excel defaults to TRUE (approximate match).

So if you write:

=VLOOKUP("EMP003", A2:D6, 2)

You might get a result that looks correct, or you might get completely the wrong answer – and you won’t get an error message to tell you something’s gone wrong.

It will just silently return the wrong data. That’s why this catches so many people out.

💡 Rule of thumb: For exact lookups, always include FALSE as the last argument.

Don’t leave it blank. It takes two seconds and prevents one of the most common (and hardest to spot) VLOOKUP mistakes.

Approximate match is genuinely useful when you need it – but your data must be sorted in ascending order for it to work correctly.

A typical use case would be something like a commission structure:

Sales From	Commission Rate
£0	5%
£10,000	7%
£25,000	10%
£50,000	12%

If someone’s sales total is £18,000 but you tried to do an exact VLOOKUP, you would get an N/A error as it can't find the exact figure of 18000 in table.

An approximate match VLOOKUP would return 7% – because £18,000 falls between £10,000 and £25,000, and VLOOKUP returns the closest value that’s less than or equal to the lookup value.

This is exactly the behaviour you want for tiered structures like this.

Approximate match example:

=VLOOKUP(18000, A2:B5, 2, TRUE)

The Most Common VLOOKUP Errors (And How to Fix Them)

N/A – “Value not found.” This means VLOOKUP couldn’t find your lookup value in the first column of your table.

Before you panic, check for the obvious culprits: extra spaces in the data (use TRIM to clean it), inconsistent formatting (text vs number – “001” stored as text won’t match 1 stored as a number), or a genuine typo.

Also check that you’re looking in the right table range.

REF! – “Column doesn’t exist.” This usually means your col_index_num is larger than the number of columns in your table range.

If your table is A to D (four columns) and you ask for column 5, you’ll get REF! Count your columns carefully.

Wrong result, no error message. This is the sneaky one. It almost always means you’ve used TRUE (approximate match) when you meant to use FALSE (exact match), or you left the last argument out entirely.

Check your formula and add FALSE.

Can’t look left. VLOOKUP can only search the first column of your table range and return values from columns to the right.

If the value you need is to the left of your lookup column, VLOOKUP simply can’t do it.

This is one of VLOOKUP’s fundamental limitations – and one of the reasons XLOOKUP was created.

The IFNA Trick: Making Your Formulas User-Friendly

Here’s a really practical tip that’s especially useful if your spreadsheet is shared with other people.

When VLOOKUP can’t find a match, it returns N/A. That’s fine when you’re building the spreadsheet yourself – you know what it means.

But when someone else sees N/A in a cell, they often think the formula is broken. It looks like an error, it feels like an error, and it causes unnecessary confusion.

The fix is to wrap your VLOOKUP inside an IFNA function. IFNA checks whether a formula returns N/A, and if it does, it shows a custom message instead:

Instead of #N/A, show a helpful message:

=IFNA(VLOOKUP("EMP099", A2:D6, 2, FALSE), "Employee not found")

Now, instead of a scary-looking error, the cell shows “Employee not found” – which tells the user exactly what’s happening.

The formula isn’t broken. There just isn’t a matching record.

This is particularly useful in shared workbooks where people might be entering lookup values manually.

If someone types an employee ID that doesn’t exist yet, they’ll see a clear, friendly message rather than an error code that makes them think the spreadsheet has a problem.

You can customise the message to whatever makes sense: “No match found”, “Check the ID and try again”, “Pending”, or even just leave it as an empty string (“”) if you want the cell to appear blank.

To show a blank cell instead of #N/A:

=IFNA(VLOOKUP(G2, A2:D6, 3, FALSE), "")

The Honest Truth: XLOOKUP Has Replaced VLOOKUP

Here’s something that a lot of Excel resources don’t mention upfront: VLOOKUP has been superseded.

Microsoft introduced XLOOKUP as its replacement, and once you’ve used it, you’ll understand why.

XLOOKUP is simpler to write, more flexible, and avoids most of the problems that make VLOOKUP frustrating. Here’s the same employee lookup in both:

VLOOKUP:

=VLOOKUP("EMP003", A2:D6, 2, FALSE)

XLOOKUP:

=XLOOKUP("EMP003", A2:A6, B2:B6, "Not found")

The differences might look subtle, but they’re significant:

No column numbers. Instead of counting columns (was it column 2 or column 3?), you just tell XLOOKUP which column to search and which column to return from. Much harder to get wrong.

It can look in any direction. XLOOKUP can look left, right, up, or down. The “can’t look left” limitation of VLOOKUP is gone entirely.

Built-in error handling. That third argument (“Not found”) is a built-in “if not found” message. No need to wrap it in IFNA – it’s already there.

Exact match by default. XLOOKUP defaults to an exact match, which is what you want most of the time. No more accidentally leaving out FALSE and getting the wrong answer.

ℹ️  Compatibility note:

XLOOKUP is available in Microsoft 365 and Excel 2021 onwards.

If you’re on an older version, or working with workbooks that were built using VLOOKUP, you’ll still need to know VLOOKUP – which is why it’s worth understanding both.

But if you’re starting fresh and have access to XLOOKUP, use it. It’s better in almost every way.

Quick Reference: Getting VLOOKUP Right Every Time

If you take nothing else away from this post, remember these four things:

1. Always use FALSE for exact matches. Don’t leave the last argument blank. It takes two seconds and prevents the most common error.

2. Check your data for hidden problems. Extra spaces, text-vs-number mismatches, and typos cause most N/A errors. TRIM and consistent formatting are your friends.

3. Use IFNA in shared workbooks. Replace N/A with a human-readable message so other people don’t think the spreadsheet is broken.

4. Learn XLOOKUP when you’re ready. It’s the modern replacement and it’s genuinely better. But knowing VLOOKUP is still essential for working with existing spreadsheets.

If you’d like to get confident with VLOOKUP, XLOOKUP, and all the key Excel skills that make a real difference at work, take a look at my Microsoft Excel training courses.

I run sessions from beginner through to advanced, and VLOOKUP is one of the core topics I cover in my intermediate course – along with plenty of other functions and techniques that’ll save you time every day.

Check out the course page for upcoming public dates - or contact me to discuss training for your team.