Last updated on November 9th, 2026 at 01:40 pm
VLOOKUP finds a value in the first column of a table and returns something from the same row, such as looking up a product ID to get its price. The formula is =VLOOKUP(what to find, where to look, which column to return, FALSE). The FALSE at the end asks for an exact match, and leaving it out is the most common reason VLOOKUP returns the wrong answer.

The example
Say you have a price list in columns E to G: product ID in E, product name in F, and price in G. In column A you have a list of product IDs from an order, and you want each one’s price in column B.
How to write a VLOOKUP, step by step
- Click cell B2, where you want the first price to appear.
- Type
=VLOOKUP( - Lookup value: click A2, the ID you want to find, then type a comma.
- Table array: select the price list, E2:G50. Press F4 once to turn it into
$E$2:$G$50so it doesn’t shift when you copy the formula down. Type a comma. Each further press of F4 switches to a different lock, which the guide to absolute cell references in Excel walks through. - Column index number: type 3, because price is the third column of the range E:G. Type a comma.
- Range lookup: type FALSE for an exact match, then close the bracket and press Enter.
The finished formula is:
=VLOOKUP(A2,$E$2:$G$50,3,FALSE)
Double-click the small square at the bottom-right of B2 to copy the formula down the rest of the column. Each row looks up its own ID.
Can VLOOKUP use wildcards?
Yes, with an exact match. =VLOOKUP("*smith*",$E$2:$G$50,2,FALSE) finds the first entry containing “smith.”
What each part means
- Lookup value is what you’re searching for. It can be a cell reference, a number, or text in quotes, like
"SKU-104". - Table array is the range to search. VLOOKUP only searches its first column, so the value you’re looking for has to be on the left.
- Column index number counts from the first column of the table array, not from column A. In E:G, E is 1, F is 2, and G is 3.
- Range lookup is
FALSE(or 0) for an exact match, orTRUEfor an approximate match. If you leave it out, Excel uses TRUE.
Exact match vs. approximate match
Use FALSE almost every time. With TRUE, VLOOKUP assumes the first column is sorted smallest to largest and returns the closest value that isn’t larger than the one you’re looking for. That’s useful for tax brackets, grading scales, or shipping rates by weight, but on an unsorted list it quietly returns the wrong row.
Example of a valid approximate match: with score thresholds 0, 60, 70, 80, 90 sorted in column E and grades F, D, C, B, A in column F, =VLOOKUP(A2,$E$2:$F$6,2,TRUE) turns a score of 84 into a B.
VLOOKUP from another sheet
The price list doesn’t have to be on the same sheet. While writing the formula, click the other sheet’s tab and select the range there. Excel writes the sheet name for you:
=VLOOKUP(A2,Prices!$A$2:$C$50,3,FALSE)
Looking up from a different workbook works in a similar way, as shown in doing a VLOOKUP between two sheets.
Fixing common VLOOKUP errors
#N/A
Excel couldn’t find the lookup value. Check for:
- Extra spaces. “SKU-104 ” with a trailing space doesn’t match “SKU-104”. Clean the data with
=TRIM(A2). - Numbers stored as text. A green triangle in the corner of a cell is a clue. Select the cells, click the warning icon, and choose Convert to Number.
- The value isn’t in the first column. VLOOKUP can’t look to the left. Move the column, or use XLOOKUP instead. In Excel 2019 and earlier, INDEX and MATCH can also look to the left.
- The value really isn’t there. To show a friendly message instead of #N/A, wrap the formula:
=IFERROR(VLOOKUP(A2,$E$2:$G$50,3,FALSE),"Not found").
#REF!
The column index number is larger than the number of columns in the table array. If the range is E:G, the highest you can use is 3.
Wrong results
Usually the last argument is missing or set to TRUE on unsorted data. Add FALSE. If the results are right in the first row but wrong further down, the table range wasn’t locked with dollar signs. How those signs work is explained in keeping one cell constant in Excel.
Results break after inserting a column
The column index number is fixed, so inserting a column inside the table shifts the data but not the number. Update the number, or switch to XLOOKUP, which doesn’t use one.
Return more than one column
To pull the name and the price at once, change the column index for each copy of the formula, or in Microsoft 365 use an array: =VLOOKUP(A2,$E$2:$G$50,{2,3},FALSE) spills both values into two cells. Other options are in using VLOOKUP for multiple columns.
Should you use XLOOKUP instead?
If you have Microsoft 365 or Excel 2021 or later, XLOOKUP is easier: =XLOOKUP(A2,$E$2:$E$50,$G$2:$G$50,"Not found"). It looks in any direction, defaults to an exact match, and doesn’t break when you insert columns. VLOOKUP is still worth knowing because it works in every version of Excel and in files other people send you. To try the newer function, start with doing an XLOOKUP in Excel.

Matt Jacobs writes and maintains the guides on Support Your Tech, which he started in 2017. He spent most of his career keeping small-business PCs running and still takes the occasional consulting job, so the steps here come from fixing the same problems on other people’s computers. Every guide is checked on his own Windows 11, Windows 10, Mac, iPhone and iPad before it goes up.