Explore Courses

How to Use VLOOKUP in Excel: A Step-by-Step Guide for Beginners


How to Use VLOOKUP in Excel: A Step-by-Step Guide for Beginners

If you have ever stared at two spreadsheets and manually copied numbers from one into the other, you already know why VLOOKUP exists. It is one of the first "real" formulas most people learn in Excel, and for good reason: it turns a slow, error-prone task into a single line of logic that runs in a fraction of a second.


If you want to learn everything about Excel, click here:

This guide walks through exactly how VLOOKUP works, how to build your first formula from scratch, and how to avoid the mistakes that trip up almost everyone the first time around.

What VLOOKUP Actually Does

VLOOKUP stands for "vertical lookup." It searches down the first column of a table for a value you specify, and when it finds a match, it pulls back a value from another column in that same row.

Think of it like using an index in the back of a book. You look up a topic (the value you're searching for), find the page number next to it (the matching row), and turn to that page to get the information you actually want (the value VLOOKUP returns).

A common real-world example: you have a list of order numbers on one sheet and a master list of customer names, cities, and order totals on another. Instead of scrolling back and forth to find the details for each order, VLOOKUP pulls that information into your working sheet automatically.

The VLOOKUP Formula, Explained Piece by Piece

The formula has four parts:

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
  • lookup_value — the thing you're searching for (an order number, a name, a product code)
  • table_array — the range of cells where the search happens, including both the column you're searching and the columns you want data from
  • col_index_num — a number that tells Excel which column, counting from the left edge of your table_array, holds the value you want returned
  • range_lookup — TRUE for an approximate match, FALSE for an exact match

That last argument is the one that causes the most confusion, so it gets its own section below. For now, just know this: in the vast majority of everyday use cases, you want FALSE.

Step-by-Step: Building Your First VLOOKUP

Let's say you have a "Master List" sheet with Product ID in column A, Product Name in column B, and Price in column C. On a separate "Orders" sheet, you have a list of Product IDs in column A, and you want Excel to pull in the matching price in column B.

Step 1: Click the cell where you want the result to appear. On the Orders sheet, that's cell B2, next to your first Product ID.

If you want to learn everything about Excel, click here:


Step 2: Start typing the formula. Type =VLOOKUP( and Excel will show you the argument list as a guide.



Step 3: Select your lookup value. Click on cell A2 (the Product ID you're matching against). Your formula now reads =VLOOKUP(A2,.



Step 4: Select your table array. Switch to the Master List sheet and highlight the full range that contains both the Product ID column and the Price column — for example, A2:C500. Press F4 to lock the reference with dollar signs ($A$2:$C$500), so it doesn't shift when you copy the formula down later.


If you want to learn everything about Excel, click here:

Step 5: Enter the column index number. Count columns starting from the left edge of the range you just selected. Product ID is column 1, Product Name is column 2, Price is column 3. Since you want the price, type 3.



Step 6: Choose exact match. Type FALSE (or just 0) as the last argument. Your finished formula looks like this:



=VLOOKUP(A2, MasterList!$A$2:$C$500, 3, FALSE)

Step 7: Press Enter, then copy the formula down. Click the small square at the bottom-right corner of the cell and drag it down through the rest of your list, or copy and paste the formula into the remaining rows. Because you locked the table array with F4, every row will still point to the correct range.

That's it — every row in your Orders sheet now automatically shows the matching price from your Master List.

TRUE vs. FALSE: Why This Argument Matters So Much

This is where most VLOOKUP mistakes happen, and it's worth slowing down on.

  • FALSE (exact match): Excel only returns a result if it finds a value in the table that matches your lookup value exactly. If there's no match, you get a #N/A error. This is what you want almost every time you're matching IDs, names, or codes.
  • TRUE (approximate match): Excel finds the closest value that is less than or equal to your lookup value. This only works correctly if the first column of your table is sorted in ascending order, and it's mainly useful for things like tax brackets or grading scales, where you're matching a number into a range rather than looking for an exact hit.

Here's the part that catches people off guard: if you leave the fourth argument out entirely, Excel assumes you meant TRUE. That means a formula like =VLOOKUP(A2, B2:D500, 2) can silently return the wrong value instead of an error, because it's quietly matching approximately instead of exactly. Since a wrong number is far more dangerous than a visible error, it's good practice to always type FALSE or TRUE explicitly rather than skip it.

Common VLOOKUP Errors and How to Fix Them

#N/A — "I can't find a match" This is the most common one. It usually means one of three things: the value genuinely doesn't exist in your table, there's an extra space or invisible character in one of the cells, or the two values are stored as different data types (one as text, one as a number). Wrapping your lookup value or table column in TRIM() or converting numbers stored as text with VALUE() often solves this.

#REF! — "That column doesn't exist" This happens when your col_index_num is higher than the number of columns in your table_array. If your table only has 3 columns and you asked for column 5, Excel has nowhere to look.

#VALUE! — "Something's wrong with an argument" Usually caused by a col_index_num that's zero, negative, or text instead of a number.

Formula returns the wrong value entirely Almost always a range_lookup issue — Excel defaulted to an approximate match when you needed an exact one. Add FALSE explicitly and the value should correct itself.

Formula breaks after you insert a new column Because col_index_num is just a static count of columns, inserting a new column into your table_array shifts everything, and your formula keeps pointing to the old position. This is one of the main reasons some Excel users prefer XLOOKUP for larger workbooks that change often — more on that below.

A Few Habits That Will Save You Headaches

  • Lock your table_array with F4 before copying the formula down, so the range doesn't shift row by row.
  • Keep your lookup column as the leftmost column in the range you select — VLOOKUP can only search left to right, never right to left.
  • Match your data types. A product code stored as text won't match the same code stored as a number, even if they look identical on screen.
  • Wrap your formula in IFERROR when you're building something you'll share with others, so a missing match shows a clean message instead of a red #N/A: 
    =IFERROR(VLOOKUP(A2, MasterList!$A$2:$C$500, 3, FALSE), "Not found")

VLOOKUP vs. XLOOKUP: Should You Switch?

If your version of Excel includes XLOOKUP (Microsoft 365 and Excel 2021 or later), it's worth knowing it exists, even if you keep using VLOOKUP for now. XLOOKUP solves VLOOKUP's two biggest limitations: it can look in any direction (not just left to right), and it references the actual return column instead of counting column numbers, so it doesn't break when you insert or delete columns.

That said, VLOOKUP isn't going anywhere. It's still the formula most templates, tutorials, and coworkers will assume you know, and for straightforward left-to-right lookups it does the job just as well. Learning it properly first makes XLOOKUP much easier to pick up later, since the underlying logic — lookup value, search range, return value — is the same.

Frequently Asked Questions

Can VLOOKUP look up more than one value at a time? Not directly, but you can copy the same formula down a column to look up a whole list at once, as shown in the tutorial above.

Why does VLOOKUP only return the first match? If your lookup value appears more than once in the table, VLOOKUP always returns the result from the first matching row it finds, top to bottom, and ignores the rest.

Can I use VLOOKUP across two different workbooks? Yes. Reference the other file by including its name in square brackets before the sheet name, though it's generally more stable to keep both sheets in the same workbook when possible, since links to closed external files can break if the file gets moved or renamed.

Does VLOOKUP work with text as well as numbers? Yes, VLOOKUP treats text and numbers the same way as long as both the lookup value and the table entries are stored using the same data type.

Wrapping Up

VLOOKUP is one of those formulas that feels intimidating for about ten minutes and then becomes second nature. Once you've built a few of them, matching data between sheets stops being a chore and turns into something you barely think about. If you regularly work with large, formula-heavy workbooks, it's also worth exploring add-ins built specifically to speed up everyday Excel tasks like formula auditing and workbook comparison — small tools that pay for themselves the first time they save you from a spreadsheet error.