What is VLOOKUP? Excel Formula Guide (2026)

VLOOKUP is an Excel function that searches for a value in the first column of a table and returns a related value from the same row in another column. It's short for Vertical Lookup, distinguishing it from HLOOKUP, which searches across a row instead. It's one of the most widely used functions in Excel, and it's usually the first "real" formula people learn beyond SUM and AVERAGE.

By the end of this guide, you'll know exactly how the syntax works, when to use it, when to skip it, and what to try instead when it starts fighting back.

What Does VLOOKUP Mean?

VLOOKUP stands for Vertical Lookup. The name tells you almost everything about how it behaves.

You give it a value to search for, and it hunts through the first column of a table you specify. Once it finds a match, it grabs a related value from the same row, just from a different column you choose. According to Ablebits' VLOOKUP tutorial, the "V" exists specifically to separate it from HLOOKUP, which performs the same job but scans across a row instead of down a column.

Here's the plain-English version: imagine a table with product names in column A and prices in column B. Instead of scrolling to find "Coffee Beans" manually, you tell Excel to search for it, and VLOOKUP hands you the price sitting right next to it.

GeeksforGeeks describes it simply: VLOOKUP finds a value in the first column of a table and returns related data from another column, which speeds up how you connect information across large datasets. If you're new to Excel formulas in general, our beginner's guide to Excel functions covers the basics before you dive into lookups specifically.

The VLOOKUP Syntax, Broken Down Plainly

The formula looks like this:

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

Four parts. Let's go through each one.

1. Lookup Value

This is the thing you're searching for. It could be a product ID, a name, a date, or a cell reference. Per Ablebits, text values need to sit inside double quotes, while numbers and cell references don't.

2. Table Array

This is your search zone, the data range where VLOOKUP will look. One rule you cannot skip: the lookup column always needs to be the leftmost column in that range. W3Schools flags this directly, and it trips up more people than any other part of the formula.

3. Column Index Number

This tells Excel which column, counting from the left, holds the value you actually want returned. If your table spans columns A through D and you want data from column C, your column index number is 3, not the letter C. That mismatch between "column letter" and "column number" is where a lot of #REF! errors are born.

4. Range Lookup

This is your exact match vs approximate match switch. Type FALSE (or 0) for an exact match, which is what you want almost every time you're looking up something like an ID or a name. GeeksforGeeks recommends FALSE for precise lookups, since TRUE assumes your data is sorted and can quietly return the wrong result if it isn't.

How VLOOKUP Actually Works (Step-by-Step)

Let's build one together. Say you have a small pricing table:

You want to know the price of "Banana" without scanning the list yourself. Here's how:

Step 1: Pick your lookup value.
Type "Banana" into a cell, or reference the cell where it already lives.

Step 2: Select your table array.
Highlight the full range containing both product names and prices, in this case A2:B4.

Step 3: Choose your column index number.
Since prices live in the second column of that range, your col_index_num is 2.

Step 4: Set your range lookup.
Use FALSE, since you want the exact product, not the closest alphabetical guess.

Step 5: Put it all together.
Your final formula reads:

=VLOOKUP("Banana", A2:B4, 2, FALSE)

Hit enter, and Excel returns $0.30. DataCamp's VLOOKUP tutorial walks through this exact type of example, and shows how referencing a cell instead of hardcoding the value makes your formula reusable and dynamic.

Real-World Use Cases for VLOOKUP

Looking up prices by product ID. Microsoft's own VLOOKUP documentation gives this as the textbook use case, pulling an automotive part's price from its part number.

Pulling employee data from an ID number. HR spreadsheets use this constantly to match names, departments, or salaries to employee IDs stored in a master sheet.

Financial modeling and scenario switching. Corporate Finance Institute shows how analysts use VLOOKUP to switch between interest rate scenarios (low, medium, high) in a debt schedule, letting one formula update the entire model based on a single dropdown choice. Our Excel for financial modeling guide covers more formulas like this one.

Merging data across sheets or workbooks. VLOOKUP can reach into a different tab, or even a different file entirely, to grab matching data without manual copy-pasting.

Data validation and cleanup. Comparing two lists to spot mismatches, missing entries, or duplicate records.

Common VLOOKUP Errors (And How to Fix Them)

#N/A error. This means Excel couldn't find your lookup value in the first column of your table array. Usually it's a typo, a stray space, or the lookup column not actually being the leftmost one.

Before running a lookup on messy data, it's worth cleaning out repeated rows first. Our guide on how to remove duplicates in Excel covers exactly that.

Wrong value returned. This almost always traces back to range_lookup being set to TRUE (or left blank) when you meant FALSE. PerfectXL's VLOOKUP guide explains this well, showing how an approximate match will happily return the "closest" alphabetical match instead of telling you nothing exists.

#REF! error. Your column index number points past the edge of your table array. Double check the range actually contains that many columns.

Formula breaks after inserting a column. This is a structural weakness of VLOOKUP itself. PerfectXL calls it a "vulnerable function" for exactly this reason, since inserting a new column shifts your column index number without updating the formula automatically.

VLOOKUP vs Its Alternatives

VLOOKUP isn't the only option, and it's not always the best one anymore.

INDEX MATCH has been the go-to workaround for years among people who wanted more flexibility, since it can pull data from a column to the left of the lookup column, something VLOOKUP simply cannot do.

XLOOKUP is Microsoft's answer to this exact problem. Microsoft Support directly recommends it over VLOOKUP now, describing it as working in any direction and returning exact matches by default, which removes one of VLOOKUP's most common pitfalls.

So is everyone actually switching? Not quite yet. Row Zero's 2026 comparison points to Google Trends data showing VLOOKUP still gets meaningfully more search interest than XLOOKUP, though that gap has been closing steadily as more people move to Excel 365. If you're working in an older version of Excel, or collaborating with people who aren't on Excel 365 or 2021, VLOOKUP is still the one that will actually open and calculate correctly on their machine. Read our full INDEX MATCH vs VLOOKUP guide if you want the left-lookup workaround in more depth.

When You Should (and Shouldn't) Use VLOOKUP

Use VLOOKUP when your lookup column is genuinely the leftmost column in your data, when your dataset is relatively small to medium sized, and when you know everyone opening the file has a compatible Excel version.

Skip VLOOKUP when you need to pull data from a column to the left of your lookup value, when your spreadsheet structure changes often, or when you're already on Excel 365 and XLOOKUP is available.

Frequently Asked Questions About VLOOKUP

What does VLOOKUP stand for?
It stands for Vertical Lookup, referring to how it searches down a column rather than across a row.

Why does my VLOOKUP formula return #N/A?
Usually because the lookup value doesn't exist in the first column of your table array, or there's a formatting mismatch like extra spaces or text stored as a number.

Can VLOOKUP look up values to the left?
No. This is a structural limitation. If you need that, use INDEX MATCH or XLOOKUP instead.

Is VLOOKUP still relevant in 2026?
Yes, particularly for compatibility across older Excel files, though Microsoft now recommends XLOOKUP for new spreadsheets when it's available, and it's gaining adoption as more teams move to Excel 365.

What's the difference between TRUE and FALSE in VLOOKUP?
FALSE gives you an exact match only. TRUE gives you an approximate match and assumes your data is sorted, which can return misleading results if it isn't.

Wrapping Up

VLOOKUP looks intimidating the first time you see that four-part syntax staring back at you, but once you break it down into "what am I searching for, where am I searching, which column do I want, and do I need an exact match," it stops being complicated fast.

Start small. Build a two-column test table like the banana example above, get comfortable with the four arguments, then move on to real spreadsheets. Once VLOOKUP clicks for you, functions like INDEX MATCH and XLOOKUP will feel like a natural next step rather than a whole new mountain to climb.