VLOOKUP in Excel Explained, Step by Step

Your phone’s contacts work this way: you type a name, and the number appears right beside it.

That’s the idea behind VLOOKUP (short for vertical lookup), an Excel function that takes a value, searches the first column of a table for it, and returns a matching value from another column in the same row.

If you spend your day in spreadsheets, you’ve probably copied a price or an ID by hand more times than you’d like.

In this post, we’ll cover how the formula is built, walk through an example step by step, and show where it falls short, along with keyboard-first tips that let you build lookups without touching the mouse.

How the VLOOKUP Excel Function Works

Here’s VLOOKUP explained for beginners. We’ll start with the big idea, then the four pieces in every formula.

How to explain VLOOKUP to dummies?

Picture a home office bookkeeper with client codes in one column and fees in another. Rather than scrolling, they tell Excel which code to find and which column to read.

VLOOKUP is a premade Excel function, so you don’t build the search yourself. It searches a table for a value and returns the matching value from the column you name, on the same row.

What each argument in the formula means

A VLOOKUP formula has four arguments, typed in this order:

1. lookup_value: what you’re searching for, like a client code in A2.
2. table_array: the range holding your data, with the search column on the far left.
3. col_index_num: the column to return, counting the first column of the range as 1.
4. range_lookup: exact or approximate match, covered next.

For the bookkeeper, =VLOOKUP(A2, B2:D500, 3, FALSE) reads the code in A2, searches column B, and returns the fee from column D.

Argument separators vary by language settings. If your Excel uses semicolons, only the punctuation changes.

Choosing between exact and approximate matches

The last argument decides how strict Excel is. The real question: do you need the same value or the nearest one?

• FALSE or 0 requests an exact match. Use it for IDs, codes, and emails, where a near miss is simply wrong.
• TRUE or 1 allows an approximate match, returning the closest value that doesn’t exceed your lookup value.
• Approximate matching needs the first column sorted in ascending order, or the results can’t be trusted.
• Leave range_lookup out and Excel quietly assumes TRUE. Type FALSE on purpose.

Following a VLOOKUP Example Step by Step

Build it one argument at a time, and we’ll keep every step on the keyboard:

Assembling the formula piece by piece

1. Select the result cell. Arrow to it, then type =VLOOKUP(.
2. Pick the lookup value. Enter the cell with the ID, such as A2, then a comma.
3. Mark the table range. Type the block with the IDs and names, such as A:B, then press F4 to lock it ($A:$B).
4. Enter the column number. Count from the left edge. Names sit in the second column, so type 2.
5. Choose the match type. Type FALSE for an exact match, not an approximate one, then close the bracket and press Enter.

For a table on another sheet, put the sheet name before the range. A home office bookkeeper with client names on a tab called Customers types =VLOOKUP(A2,Customers!A:B,2,FALSE), and Ctrl+Page Down jumps there to check the range without touching the mouse.

How the result changes with your lookup

Here’s the running example: Pokémon IDs in column A, names in column B. Type an ID in the lookup cell, and the matching name appears in the result. Change the ID and it updates right away.

Clear the lookup cell and the result shows #N/A, because there’s nothing to match. Nothing is broken.

Consider a support agent who types a customer ID and reads back the name before the call connects, with no list to scroll.

Exact matches have three common traps:

• A stray space, such as one after an ID, stops the match and returns #N/A.
• Duplicates are sneakier: VLOOKUP returns the first match, not every one, and never warns you.
• And without absolute references (the dollar signs F4 adds), copying the formula down shifts the table.

Where VLOOKUP Falls Short

VLOOKUP is dependable, but it has blind spots that catch even experienced users. Knowing them helps you spot a wrong result before it reaches your report.

Why a fixed column number breaks results

Every VLOOKUP formula includes a column index number, which tells Excel how many columns to count from the lookup column. That number is fixed.

Insert a new column into the table and the formula doesn’t adjust, so it returns data from the wrong column, often with no error at all.

A home office analyst tracks stock performance in a shared workbook. Her formula pulls closing prices from column 3, and it’s been accurate for months. Then a colleague adds a Sector column in column 2.

The formula now returns sector names where prices belong, and her report still looks perfectly normal. Nothing looks broken.

If the number is larger than the table is wide, you’ll get #REF! instead, which at least flags the problem.

Why the lookup column must sit first

VLOOKUP searches only the leftmost column of your table and returns values from columns to its right. If the data you need sits to the left, it can’t reach it, so you’d have to rearrange your sheet or add a helper column.

Two alternatives skip that restriction:

• XLOOKUP takes the return column as its own argument, so column order doesn’t matter.
• INDEX and MATCH work as a pair: MATCH finds the row, and INDEX returns the value from whichever column you choose.

Some errors point to a simple mismatch, not a broken formula. #N/A usually means the value wasn’t found. A common cause is a number stored as text, which looks like a real number but won’t match one.

Convert it with the VALUE function and rerun the lookup.

Pairing VLOOKUP with MATCH and Other Options

VLOOKUP makes you count columns by hand, and that’s where it tends to break. Here’s how we let MATCH and a few related functions take that job off your plate.

How the MATCH function finds positions

MATCH returns an item’s position, not the data: its row in a vertical list or its column in a horizontal one. It takes a value, a lookup_array (the range to search), and a match_type:

• 0 finds an exact match.
• 1 finds the largest value at or below your search (data sorted ascending).
• -1 finds the smallest value at or above your search (data sorted descending).

Putting MATCH inside your VLOOKUP formula

Instead of a fixed column number, let MATCH find the column by header name. A freelance bookkeeper pulling figures from a client’s workbook might write =VLOOKUP(A2, B:F, MATCH("Amount", B1:F1, 0), FALSE).

That makes the formula dynamic: insert a column and it still grabs Amount, so the bookkeeper doesn’t have to rewrite it.

Related lookup functions like XLOOKUP and HLOOKUP

Three other options:

• XLOOKUP, Excel’s newer function, can look left as easily as right.
• INDEX and MATCH combine INDEX (returns a value at a given position) with MATCH (finds that position).
• HLOOKUP (horizontal lookup) searches the top row and returns from a row you specify.

Starting INDEX means learning =INDEX(array, row_num, [column_num], [area_num]), where row_num picks the row. The area number only matters when the reference is made of separate ranges, like (A1:B5, D1:E5).

It tells Excel which range to use, counting from 1 in the order listed, and it defaults to 1.

Is INDEX MATCH better than VLOOKUP? For tables that change, usually yes: it doesn’t rely on a fixed column count. For a small, stable table, VLOOKUP’s simplicity is hard to beat. Pick the one your team can read.

Once you know the four arguments, VLOOKUP stops feeling like a puzzle. You know what to search for, where to search, which column to pull from, and whether you want an exact match.

When a table outgrows it, XLOOKUP or INDEX/MATCH will be ready for the job, and you’ll know when to switch.

Keep your hands on the keys and your data clean, and the answers will come to you. Clean data in, reliable answers out.