No Ticket Needed

Appearance

How to Use VLOOKUP in Excel

Applies toMicrosoft 365ExcelExcel for MacExcel for the web
6 min fix Updated 29 Jul 2026

By , 10+ years in IT support and helpdesk How these guides are checked

Quick Answer

Type =VLOOKUP( then four things: the value to find, the range to search, the number of the column to return counting from the left edge of that range, and FALSE for an exact match. Example: =VLOOKUP(G2, $A$2:$D$50, 4, FALSE).

Checked against Microsoft 365 Roadmap, Office release notes - Current Channel and between 5 Aug 2026 and 12 Sep 2026. Nothing published in that period has been linked to this guide.

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.

In this guide

Before You Start ​

  • 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:

RowABCD
1Product CodeProduct NameCategoryUnit Price
2KB-1180Wireless KeyboardPeripherals42.00
3MN-274027-inch MonitorDisplays289.00
4HS-3310USB HeadsetPeripherals68.50
5DK-4025Docking StationPeripherals155.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)
ArgumentWhat it meansRequired
lookup_valueThe value you are searching for. A cell reference such as G2, or text in quotes such as "HS-3310".Yes
table_arrayThe block of cells to search. Excel looks for lookup_value in the first column of this range only.Yes
col_index_numWhich column of table_array holds the answer you want, counted from that range's left edge. 1 is the first column.Yes
range_lookupFALSE 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.

  1. Click H2 and look at the formula bar above the column headers. Click inside the A2:D5 part of the formula.
  2. Press F4. The range changes to $A$2:$D$5. On a Mac, press Command + T if F4 adjusts screen brightness instead.
  3. Extend the range to cover rows you will add later, so the formula reads:
    =VLOOKUP(G2, $A$2:$D$500, 4, FALSE)
  4. 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.

VLOOKUPXLOOKUP
Which column it returnsA number you count and maintainThe actual column, selected directly
DirectionRight of the lookup column onlyAny direction
Default match typeApproximateExact
Missing value#N/A until you add IFERRORBuilt-in fallback argument
Survives an inserted columnNoYes
Available inEvery version of ExcelMicrosoft 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 / ErrorPotential CauseSolution
#N/A on every rowLookup value is not in the range's first columnVLOOKUP 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 rangecol_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 columnA column was inserted inside the rangeThe stored column number no longer points where you think. Replace it with the MATCH pattern in Step 4.
Plausible but incorrect valuesFourth argument missing or TRUEApproximate match returns the nearest smaller value from an unsorted list. Add , FALSE as shown in Step 3.
Every row returns the same answerLookup 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 textcol_index_num must be a whole number of 1 or more. Remove any quote marks around it.

Last updated:

Frequently asked questions

What does FALSE at the end of a VLOOKUP do?
FALSE forces an exact match, so VLOOKUP returns a result only when it finds the value you asked for. TRUE (or leaving the argument out) turns on approximate match, which returns the nearest smaller value and requires the first column to be sorted A-Z. Use FALSE unless you are deliberately looking up a value in a banded range such as a tax table.
Why does my VLOOKUP return #N/A when I can see the value in the list?
The two values look identical but are not. The usual causes are a trailing space in one of the cells, or a code stored as text in one sheet and as a number in the other. Test it by typing =G2=A5 in an empty cell — if that returns FALSE when the two look the same on screen, the values genuinely differ.
Can VLOOKUP look to the left?
No. VLOOKUP always searches the first column of the range you give it and returns something to the right. To return a value that sits to the left of your lookup column, move the range so the lookup column is first, use INDEX and MATCH together, or use XLOOKUP if your version of Excel has it.