VLOOKUP function
VLOOKUP finds an item in the first column of a table and returns a value from the same row. Always type FALSE for an exact match. XLOOKUP is the modern choice.
Example
Read it as a sentence: find Cigar in the first column of B2:E7, go across to the 4th column, and accept only an exact match.
How VLOOKUP works
VLOOKUP answers a question about one row of a table: you know one thing, so what does the table say next to it? Give it a product name and get the price. Give it an employee ID and get the manager.
You give it four things: the value to find, the table, which column of the table to bring back, and whether only an exact match will do. Excel runs down the first column of the table until it reaches your value, then reads across that row to the column you asked for. The V stands for vertical, because the search goes down.
In the example above, the table is B2:E7 on the Items sheet, so its first column is B, the item names. Cigar sits in B5. Counting across, column 4 of the table is column E, Unit Value, and E5 holds 10, so that is the answer.
The last argument, FALSE, tells Excel to accept only an exact match. Leave it out and VLOOKUP settles for the nearest value below yours, which can hand you an answer from the wrong row. Type FALSE every time, unless you are sorting numbers into bands.
Syntax
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Arguments in [square brackets] are optional.
- What to look for
lookup_value(required) - Value to find in the first column of the table.
- The table, with the search column first
table_array(required) - The table to search; the lookup column must be its first column.
- Which column to bring back
col_index_num(required) - Column of the table to return, counting the first as 1.
- Exact or approximate match
range_lookup(optional) -
Use FALSE unless you are sorting numbers into bands.
FALSEExact match only. Upper and lower case don't matter.0The same as FALSE.TRUEApproximate: the largest value that is not above the lookup value. Sort the first column smallest to largest.(left out)The same as TRUE. This is the setting that turns a typo into a wrong answer.
Returns: The value from the chosen column of the matching row.
More examples
Each picture shows only the columns that formula reads.
-
Bring back a text column
Which category is the candle in?
Excel shows HouseholdBCDE1ItemCategoryQtyUnit Value2Boxer cartridgeTools12$3.503Briar pipeSmoking7$4.254CandleHousehold20$2.005CigarSmoking5$10.006Clay pipeSmoking15$1.757Commonplace bookPapers9$6.00ItemsThe answer column can hold text as easily as numbers. Column 2 of B2:E7 is Category, so Excel returns Household.
-
Look up by an ID
Who owns item I03?
Excel shows P03ABCDEFGHI1Item IDItemCategoryQtyUnit ValueOwnerKept AtAcquiredInsured2I01Boxer cartridgeTools12$3.50P01L0111/2/2024FALSE3I02Briar pipeSmoking7$4.25P01L016/18/2021FALSE4I03CandleHousehold20$2.00P03L011/6/2025FALSE5I04CigarSmoking5$10.00P01L012/11/2025FALSE6I05Clay pipeSmoking15$1.75P01L019/30/2023FALSE7I06Commonplace bookPapers9$6.00P01L014/15/2019FALSEItemsIDs make the safest lookup values, because each one appears only once. This table starts at column A, the Item ID, and column 6 of A2:I7 is Owner. Item I03 is the candle, and its owner is P03.
-
Pull a value from another sheet
What is the cab fare to Scotland Yard?
Excel shows 1.75BCDEF1PlaceTypeRegionMilesCab Fare2221B Baker StreetResidenceLondon0$0.003Scotland YardPoliceLondon2.8$1.754Diogenes ClubClubLondon1.9$1.255BartsHospitalLondon3.1$2.006PaddingtonStationLondon1.2$0.757Waterloo StationStationLondon3.4$2.258Simpson'sRestaurantLondon2.3$1.509Lyceum TheatreTheatreLondon2.2$1.5010Pondicherry LodgeResidenceLondon9.5$5.5011Briony LodgeResidenceLondon1.6$1.0012Saxe-Coburg SquareShopLondon2.9$1.7513Reichenbach FallLandmarkSwitzerland620$48.0014Baskerville HallCountry houseDevonshire215$18.5015Stoke MoranCountry houseSurrey24$3.2516Copper BeechesCountry houseHampshire68$6.75PlacesThe table lives on the Places sheet, so the range starts with the sheet name and an exclamation mark: Places!B2:F16. Column 5 of that table is Cab Fare, and Excel returns 1.75.
-
Replace #N/A with a message
Can the cell say something friendlier than #N/A?
Excel shows Not in the roomsBCDE1ItemCategoryQtyUnit Value2Boxer cartridgeTools12$3.503Briar pipeSmoking7$4.254CandleHousehold20$2.005CigarSmoking5$10.006Clay pipeSmoking15$1.757Commonplace bookPapers9$6.00ItemsVLOOKUP has no slot for a not-found message, so the search for a Deerstalker ends in #N/A. Wrap the whole VLOOKUP in IFNA, put your message second, and the cell reads Not in the rooms.
-
=VLOOKUP("Deerstalker",Items!B2:E7,4,FALSE)#N/A
-
-
Sort a number into a band
Is a 9.5-mile trip a walk, a short cab, a long cab or a train?
ABCD1Long cab23This is the job approximate match is for. The first column holds where each band starts, sorted smallest to largest: 0, 1, 5 and 50. With TRUE, Excel takes the largest start that is not above 9.5, which is 5, and returns Long cab.
-
Match part of a name with a wildcard
What is the first pipe in the list worth?
Excel shows 4.25BCDE1ItemCategoryQtyUnit Value2Boxer cartridgeTools12$3.503Briar pipeSmoking7$4.254CandleHousehold20$2.005CigarSmoking5$10.006Clay pipeSmoking15$1.757Commonplace bookPapers9$6.00ItemsIn an exact-match VLOOKUP, * is a wildcard for any characters, so *pipe fits every name ending in pipe. Two items fit, and Excel returns the first one it meets: the Briar pipe, at 4.25.
VLOOKUP or XLOOKUP?
The big difference is direction. VLOOKUP brings back only columns to the right of its search column (see It can't look to the left below). XLOOKUP takes the search column and the answer column separately, so the answer can sit on either side.
XLOOKUP also does an exact match unless told otherwise, has its own slot for the not-found text, and can search from the bottom up. VLOOKUP keeps one advantage: it works in every version of Excel, while XLOOKUP needs Excel 2021 or Microsoft 365. If the workbook goes to people on older versions, stay with VLOOKUP or INDEX and MATCH.
Watch out for
Leaving out FALSE can give a wrong answer
With the fourth argument left out, VLOOKUP does an approximate match. Look up Candel, a typo for Candle, and instead of an error you get 4.25, the value of the Briar pipe, the last item that sorts before Candel. Add FALSE and the same typo gives #N/A, which at least tells you something is wrong.
-
=VLOOKUP("Candel",Items!B2:E7,4)4.25 -
=VLOOKUP("Candel",Items!B2:E7,4,FALSE)#N/A
It can't look to the left
The column you search must be the first column of the table. When the table starts at Item ID, VLOOKUP searches the IDs for Cigar and returns #N/A. Start the table at the column you search, or switch to XLOOKUP or INDEX and MATCH.
-
=VLOOKUP("Cigar",Items!A2:I7,1,FALSE)#N/A
A stray space gives #N/A
Candle with a space after it is not the same text as Candle, so the lookup fails. TRIM strips the extra space from the lookup value, and the quantity, 20, comes back.
-
=VLOOKUP("Candle ",Items!B2:E7,3,FALSE)#N/A -
=VLOOKUP(TRIM("Candle "),Items!B2:E7,3,FALSE)20
Only the first match comes back
Smoking appears three times in the Category column. VLOOKUP stops at the first one, the Briar pipe, and returns its quantity, 7. To add up every match instead, use SUMIF.
-
=VLOOKUP("Smoking",Items!C2:E7,2,FALSE)7
The column number has to fit the table
B2:E7 is four columns wide, so asking for column 5 gives #REF!, and column 0 gives #VALUE!. Count from the left edge of the table, not from column A of the sheet.
-
=VLOOKUP("Cigar",Items!B2:E7,5,FALSE)#REF! -
=VLOOKUP("Candle",Items!B1:E7,0,FALSE)#VALUE!
A number in quotes won't find a number
The Qty column holds the number 12. Looking up "12" in quotes searches for text and ends in #N/A. Check for this when a lookup fails on IDs or codes that were pasted in from another system.
-
=VLOOKUP("12",Items!D2:E7,2,FALSE)#N/A
Dates arrive as serial numbers
Column 7 of B2:I7 is Acquired, and the cigar's date there is 2/11/2025. VLOOKUP brings back what the cell stores, 45699, without the date format, so give the formula's cell a date format to read it.
-
=VLOOKUP("Cigar",Items!B2:I7,7,FALSE)45699
Match and search options
range_lookup: exact or approximate match
Use FALSE unless you are sorting numbers into bands.
| Value | What it does | Measured example | Excel shows |
|---|---|---|---|
FALSE | Exact match only. Upper and lower case don't matter. | =VLOOKUP("Cigar",Items!B2:E7,4,FALSE) | 10 |
0 | The same as FALSE. | =VLOOKUP("Candle",Items!B1:E7,3,0) | 20 |
TRUE | Approximate: the largest value that is not above the lookup value. Sort the first column smallest to largest. | =VLOOKUP(9.5,{0,"Walk";1,"Short cab";5,"Long cab";50,"Train"},2,TRUE) | Long cab |
(left out) | The same as TRUE. This is the setting that turns a typo into a wrong answer. | =VLOOKUP("Candel",Items!B2:E7,4) | 4.25 |
Questions people ask
How do I use VLOOKUP in Excel?
Type =VLOOKUP( then the value to find, the table with the search column first, the number of the column to bring back, and FALSE. For the cigar that returns 10.
-
=VLOOKUP("Cigar",Items!B2:E7,4,FALSE)10
What does VLOOKUP do, in plain words?
It works like finding a name in a printed list: run your finger down the left-hand column to the name, then read across to the column you want.
How do I use VLOOKUP between two sheets?
Put the other sheet's name and an exclamation mark in front of the table range, as in Places!B2:F16. The rest of the formula stays the same.
-
=VLOOKUP("Scotland Yard",Places!B2:F16,5,FALSE)1.75
Can VLOOKUP check more than one condition?
Not on its own. INDEX and MATCH can, by multiplying the conditions together. This one finds the first Smoking item with more than 5 in stock, the Briar pipe.
-
=INDEX(Items!B2:B7,MATCH(1,(Items!C2:C7="Smoking")*(Items!D2:D7>5),0))Briar pipe
Is VLOOKUP case-sensitive?
No. Looking up candle in lower case still finds Candle.
-
=VLOOKUP("candle",Items!B1:E7,3,FALSE)20
Where it works
- Excel
- VLOOKUP is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Lookup and reference functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .