How to Use VLOOKUP in Excel

Applies to: Microsoft 365 (Excel for Windows, Excel for Mac, Excel for the Web); Excel 2016, 2019, 2021
Article Type: How-To
Last Updated: 2026-07-29

Summary

VLOOKUP finds a value in the first column of a table and returns something from another column in the same row — a price from a product code, a manager from an employee ID, a rate from a region name. This article walks you through the four arguments, the two traps that break most VLOOKUPs, and how to make the formula fail gracefully instead of filling your report with red errors.

Prerequisites

• Any version of Excel. VLOOKUP has been in Excel since the 1990s and works identically in Excel for Windows, Excel for Mac, and Excel for the Web.
• A table of data where the value you are looking up sits in the left-most column of the range you plan to search.
• No admin rights required.

Instructions

The examples below all use the same sheet, a tab named Price List:

Row	A	B	C	D
1	Product Code	Product Name	Category	Unit Price
2	KB-1180	Wireless Keyboard	Peripherals	42.00
3	MN-2740	27-inch Monitor	Displays	289.00
4	HS-3310	USB Headset	Peripherals	68.50
5	DK-4025	Docking Station	Peripherals	155.00

1. Understand the Four Arguments

VLOOKUP takes four pieces of information, and every one of them has a common failure mode.

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Argument	What it means	Required
lookup_value	The value you are searching for. A cell reference such as G2, or text in quotes such as "HS-3310".	Yes
table_array	The block of cells to search. Excel looks for lookup_value in the first column of this range only.	Yes
col_index_num	Which column of table_array holds the answer you want, counted from that range's left edge. 1 is the first column.	Yes
range_lookup	FALSE for an exact match, TRUE for approximate. Excel treats a missing fourth argument as TRUE.	Technically no — always supply it

Read out loud, a VLOOKUP says: find this value in the first column of this range, and hand me whatever sits in column number N of that same row.

2. Build Your First VLOOKUP

Look up a unit price from a product code using a real price list.

1. Open the Price List tab and click cell G2. Type HS-3310 and press Enter. This is the code you want a price for.
2. Click cell H2.
3. Type the formula:

   =VLOOKUP(G2, A2:D5, 4, FALSE)

4. Press Enter. Cell H2 now shows 68.50 — the value from column D on the USB Headset row.

Now lock the range so you can copy the formula down without it drifting.

5. Click H2 and look at the formula bar above the column headers. Click inside the A2:D5 part of the formula.
6. Press F4. The range changes to $A$2:$D$5. On a Mac, press Command + T if F4 adjusts screen brightness instead.
7. Extend the range to cover rows you will add later, so the formula reads:

   =VLOOKUP(G2, $A$2:$D$500, 4, FALSE)

8. Press Enter, then drag the small square at the bottom-right corner of H2 down the column.

Verification: Each row returns the price for its own product code. If every row shows the same number, the G2 reference got locked by mistake — it must stay G2, not $G$2, so it moves down with the formula.

Note: The dollar signs are what stop the range drifting one row down with every copy. If that behaviour is new to you, Understanding Absolute and Relative Cell References in Excel explains which half of a reference each one locks.

3. Set the Fourth Argument to FALSE Every Time

Leaving the last argument blank turns on approximate match, which returns wrong answers without warning you.

With TRUE or nothing at all in the fourth slot, VLOOKUP does not look for your value. It walks down the first column until it passes your value, then hands back the row before it — and it assumes the column is sorted A-Z. On an unsorted price list that produces a real-looking price for the wrong product, with no error to tell you.

1. Look at any VLOOKUP in your workbook and count the commas. Three commas means four arguments and you are covered.
2. If the formula ends ..., 4) with no fourth argument, click into the cell, click in the formula bar, and change the ending to ..., 4, FALSE).
3. Press Enter. Values that were silently wrong now either correct themselves or turn into #N/A — which is the honest answer, and Step 6 makes it presentable.

Note: Approximate match has one legitimate use: banded ranges such as tax brackets, commission tiers, or postage weights, where you want "the row that covers this number". In that case sort the first column smallest to largest and use TRUE deliberately.

4. Count Columns from the Left Edge of the Range

The column number counts from the first column of your range, not from column A of the sheet.

This is the single most common VLOOKUP mistake. If your range is $B$2:$E$500, then column B is 1, C is 2, D is 3, E is 4 — even though B is the second column of the worksheet.

There is a second version of the same trap. A colleague inserts a new column inside your range next week; the data shifts right, col_index_num stays at 4, and the formula quietly starts returning the Category instead of the Unit Price. Nothing turns red.

Fix it by calculating the column number from the header text instead of typing it:

=VLOOKUP($G2, $A$2:$D$500, MATCH("Unit Price", $A$1:$D$1, 0), FALSE)

1. MATCH("Unit Price", $A$1:$D$1, 0) finds which column of the header row is labelled Unit Price and returns its position — 4 today.
2. When someone inserts a column, MATCH finds Unit Price in its new position and returns 5 on its own. The formula keeps working.
3. Type the header text exactly as it appears in row 1, including capitals and spaces.

Tip: Also convert the data to a table — click any cell in it and press Ctrl + T, then OK. New rows typed at the bottom are pulled into the range automatically, so your VLOOKUP range never falls behind the data.

5. Work Around VLOOKUP's Left-Hand Blind Spot

VLOOKUP can only return data to the right of the column it searches. Here are three ways around it.

Searching for Wireless Keyboard in column B and asking for its Product Code in column A does not work, because column A is to the left. Pick whichever fix suits the workbook:

• Re-point the range. VLOOKUP does not need to start at column A. To look up a Product Name and return its Category, start the range at column B: =VLOOKUP(G2, $B$2:$D$500, 2, FALSE). The lookup column has to be first in the range, not first in the sheet.
• Use INDEX and MATCH. This pair searches in any direction and works in every version of Excel:

  =INDEX($A$2:$A$500, MATCH($G$2, $B$2:$B$500, 0))

  Read it as: find which row of column B holds this value, then give me that row of column A.
• Use XLOOKUP if your version has it — see Step 7.

Note: Copying the lookup column so a duplicate sits on the left also works, and it is what many shared workbooks end up doing. It costs you a column that has to stay in sync, so treat it as a last resort rather than a first move.

6. Catch Missing Matches with IFERROR

Replace the red #N/A errors with a message a human can read.

A #N/A means VLOOKUP searched and did not find the value. That is normal in a live workbook — a new product code, a blank row in the input column — but a column of red errors makes a report look broken and stops SUM from working further down.

1. Click the cell holding your working VLOOKUP.
2. In the formula bar, wrap the whole formula in IFERROR( and add your fallback text at the end:

   =IFERROR(VLOOKUP(G2, $A$2:$D$500, 4, FALSE), "Not in price list")

3. Press Enter. Rows that match still show the price; rows that do not now read Not in price list.
4. To leave the cell looking empty instead, use two quote marks with nothing between them: ..., FALSE), "").

IFNA is the sharper tool where you have it (Excel 2013 and later). It hides only #N/A and lets genuine mistakes such as #REF! stay visible:

=IFNA(VLOOKUP(G2, $A$2:$D$500, 4, FALSE), "Not in price list")

Warning: Build and test the bare VLOOKUP first, then wrap it. IFERROR swallows every error type, so a formula with a broken range or a bad column number looks exactly like a clean "no match found" — and you lose the error that would have told you.

7. Decide Whether to Switch to XLOOKUP

XLOOKUP is the better function where it exists, but VLOOKUP is still the right choice for shared and older workbooks.

	VLOOKUP	XLOOKUP
Which column it returns	A number you count and maintain	The actual column, selected directly
Direction	Right of the lookup column only	Any direction
Default match type	Approximate	Exact
Missing value	#N/A until you add IFERROR	Built-in fallback argument
Survives an inserted column	No	Yes
Available in	Every version of Excel	Microsoft 365 and Excel 2021 or later

The same lookup written both ways:

=IFERROR(VLOOKUP(G2, $A$2:$D$500, 4, FALSE), "Not in price list")

=XLOOKUP(G2, $A$2:$A$500, $D$2:$D$500, "Not in price list")

Stay on VLOOKUP when any of these apply:

• Your company is on Excel 2019 or earlier. XLOOKUP does not exist there and returns #NAME?.
• You share the workbook with customers, clients, or a team on mixed versions. Anyone opening it in an older Excel sees #NAME? in every XLOOKUP cell, and saving the file in that state can strip the formula.
• The workbook already runs on VLOOKUP and works. Rewriting hundreds of formulas introduces more risk than it removes.

Move to XLOOKUP when everyone touching the file is on Microsoft 365 or Excel 2021 and later, and start new workbooks there. Full walkthrough: How to Use XLOOKUP in Excel.

Troubleshooting

INFO

A #N/A from VLOOKUP means "I searched and did not find it" — not that the formula is broken. Most of the time the cause is invisible on screen: a trailing space in one of the cells, or a code stored as text on one sheet and as a number on the other. Type =G2=A5 in an empty cell to compare the two directly. A result of FALSE when they look identical confirms the values differ, and wrapping the lookup value in TRIM — =VLOOKUP(TRIM(G2), ...) — clears stray spaces.

Symptom / Error	Potential Cause	Solution
#N/A on every row	Lookup value is not in the range's first column	VLOOKUP searches only the left-most column of table_array. Re-point the range so that column comes first, or use the INDEX and MATCH pattern in Step 5.
#REF!	Column number is bigger than the range	col_index_num of 5 against a four-column range has nowhere to look. Widen the range or lower the number — see Step 4.
Right row, wrong column	A column was inserted inside the range	The stored column number no longer points where you think. Replace it with the MATCH pattern in Step 4.
Plausible but incorrect values	Fourth argument missing or TRUE	Approximate match returns the nearest smaller value from an unsorted list. Add , FALSE as shown in Step 3.
Every row returns the same answer	Lookup value locked with $	Click the cell, and in the formula bar change $G$2 back to G2 so it moves down with the fill.
#VALUE!	Column number is 0, negative, or text	col_index_num must be a whole number of 1 or more. Remove any quote marks around it.

Related Articles

• How to Use XLOOKUP in Excel
• How to Use IF and IFS Functions in Excel
• How to Use SUMIF and COUNTIF in Excel
• How to Fix Common Excel Formula Errors
• Understanding Absolute and Relative Cell References in Excel
• How to Sort and Filter Data in Excel
• Understanding Excel Features — A Quick-Reference Guide to What Excel Can Do

Did this solve your problem?