How to Do a VLOOKUP in Excel: A Full Guide

To do a VLOOKUP in Excel, type =VLOOKUP( in a cell and add four parts. These are the value to find, the table to search, the column number to return, and FALSE for an exact match. For example, =VLOOKUP(B2,$F$2:$H$5,3,FALSE) finds the value in B2 and returns the third column of the table.
This guide covers how VLOOKUP works, how to build one step by step, and how to look up values from another sheet. It also covers exact and approximate matches, common errors, and when to use XLOOKUP instead. Steps come from Microsoft's support pages, read on November 13, 2026.
What VLOOKUP does
VLOOKUP finds a value in the first column of a table and returns a value from the same row. The "V" stands for vertical, because it searches down a column.
Microsoft's support page gives two typical uses. You can "look up a price of an automotive part by the part number." You can also "find an employee name based on their employee ID."
Common jobs include:
- Prices. Pull a product's price into an order list by its product code.
- Names. Bring an employee's name into a report by employee ID.
- Categories. Add a region or department to each row of a sales export.
- Rates. Find a tax rate or commission rate from a lookup table.
- Matching lists. Check which items in one list appear in another.
VLOOKUP has one rule that shapes everything else. Microsoft calls it "The secret to VLOOKUP." You organize the data so the value you look up "is to the left of the return value (Amount) you want to find."
How the VLOOKUP formula works
The syntax has four parts. Microsoft lists it as:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
| Part | What it means | Example |
|---|---|---|
| lookup_value | The value you want to find | B2, the product code |
| table_array | The range to search, with the lookup value in its first column | $F$2:$H$5 |
| col_index_num | The column in that range to return, counting from 1 | 3, for the price column |
| range_lookup | FALSE for an exact match, TRUE for an approximate match | FALSE |
Microsoft explains the column count with an example. With B2:D11 as the range, "you should count B as the first column, C as the second, and so on."
The last part is optional, but leaving it out is risky. Microsoft says "If you don't specify anything, the default value will always be TRUE or approximate match." For most lookups, such as IDs and codes, you want FALSE.
Set up your data first
Most VLOOKUP problems start with the data, not the formula. A few checks save time later.
- Put the lookup column first. The value you search for must be in the left-most column of the table range.
- Keep one value per row. Each code or ID should appear once in the lookup table.
- Store numbers as numbers. Microsoft warns that if numbers are stored as text, "VLOOKUP might return an incorrect or unexpected value."
- Remove extra spaces. Leading or trailing spaces stop matches. The TRIM function removes them.
- Use headers. Clear headers make it easier to count the column number.
If your lookup column sits to the right of the value you need, VLOOKUP cannot reach it. You can move the column, or use XLOOKUP, which is covered later.
Two more habits make lookups easier to maintain. First, format the lookup table as an Excel table, or give it a name. Microsoft notes that for table_array "You can use a named range or a table." A named range such as Products is easier to read than $F$2:$H$5. Second, keep lookup tables on their own sheet. Then nobody sorts or deletes rows by accident while editing the main data.
How to do a VLOOKUP step by step
Here is the full process for a basic exact-match lookup. In this example, an order list sits in columns A to C, and a product table sits in columns F to H.
- Click the cell for the result. For example, D2, next to the first order.
- Start the formula. Type =VLOOKUP(.
- Add the lookup value. Click B2, the product code, then type a comma.
- Add the table range. Select F2:H5, press F4 to make it $F$2:$H$5, then type a comma.
- Add the column number. Type 3 for the price column, then a comma.
- Choose the match type. Type FALSE, close the bracket, and press Enter.
The finished formula is =VLOOKUP(B2,$F$2:$H$5,3,FALSE).
Then fill the formula down the column. Double-click the fill handle in the corner of D2, and Excel copies it to the last row.
Step 4 matters for filling. Microsoft's best practices say to use absolute references, so you can "fill-down a formula so that it always looks at the same exact lookup range." Without the dollar signs, the range shifts down one row each time, and later lookups miss.
VLOOKUP between two sheets
Lookup tables often live on another sheet. VLOOKUP handles that with a sheet name in the range.
Microsoft's page includes this example:
=VLOOKUP(A2,'Client Details'!A:F,3,FALSE)
Here, 'Client Details' is the sheet name, in single quotes because it contains a space. A:F is the range on that sheet. The formula looks up A2 and returns the third column.
The easiest way to build it is by clicking:
- Start typing =VLOOKUP( and click the lookup cell.
- Type a comma, then click the other sheet's tab.
- Select the table range on that sheet, and press F4 to lock it.
- Finish the column number and FALSE, then press Enter.
Excel writes the sheet name for you. The same method works for another workbook, as long as both files are open while you build the formula.
Exact match vs. approximate match
The fourth argument changes how VLOOKUP searches. Choosing the wrong one is a common source of wrong answers.
Exact match (FALSE or 0). Microsoft says it "searches for the exact value in the first column." If the value is not there, the result is #N/A. Use it for IDs, codes, names, and anything that must match exactly.
Approximate match (TRUE or 1). Microsoft says it "assumes the first column in the table is sorted either numerically or alphabetically, and will then search for the closest value." Use it for ranges, such as tax brackets or grade boundaries.
Here is a typical approximate-match case. A commission table lists sales thresholds of 0, 10,000, and 50,000, with rates of 2%, 3%, and 5%. A sale of 32,000 falls between 10,000 and 50,000, so VLOOKUP returns 3%.
For this to work, the thresholds must be sorted from smallest to largest. Microsoft warns that if the first column isn't sorted, "the return value might be something you don't expect."
Exact matches also support wildcards for text. Microsoft says that with FALSE and a text lookup value, you can use the question mark and asterisk. "A question mark matches any single character. An asterisk matches any sequence of characters." Its example =VLOOKUP("Fontan?",B2:E7,2,FALSE) finds Fontana with any last letter. To search for a real question mark or asterisk, type a tilde (~) in front of it.
Fix common VLOOKUP errors
Microsoft's page lists the most common problems and what causes them.
| Error | What went wrong | How to fix it |
|---|---|---|
| #N/A | With FALSE, the exact value was not found | Check spelling, spaces, and text-stored numbers |
| #REF! | The column number is larger than the number of columns in the range | Widen the range or lower the column number |
| #VALUE! | An argument is less than 1 | Check that the column number is 1 or more |
| #NAME? | Usually missing quotes around text in the formula | Write text in quotes, such as "Fontana" |
| Wrong value | TRUE or a blank match type on an unsorted column | Use FALSE, or sort the first column |
For #N/A results that are expected, such as new products not yet in the table, wrap the formula in IFERROR:
=IFERROR(VLOOKUP(B2,$F$2:$H$5,3,FALSE),"Not found")
This shows a clear label instead of an error. Use it with care. A label can hide a real data problem, such as a misspelled code.
Microsoft also covers a #SPILL! error in newer Excel. It happens when the lookup value refers to a whole column, such as A:A. Refer to a single cell, such as A2, instead.
VLOOKUP vs. XLOOKUP
Microsoft now suggests a newer function. Its VLOOKUP page calls XLOOKUP "an improved version of VLOOKUP." It adds that XLOOKUP "works in any direction and returns exact matches by default."
XLOOKUP's syntax is:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
The same price lookup in XLOOKUP is =XLOOKUP(B2,$F$2:$F$5,$H$2:$H$5). There is no column number to count.
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Direction | Lookup column must be on the left | Return column can be on either side |
| Default match | Approximate (TRUE) | Exact |
| Column reference | A number you count | A range you select |
| Not-found message | Needs IFERROR | Built-in if_not_found argument |
| Availability | All versions | Not in Excel 2016 or Excel 2019 |
The last row matters. Microsoft notes that "XLOOKUP is not available in Excel 2016 and Excel 2019." If you share files with people on those versions, VLOOKUP is still the safer choice.
There is a third option for older versions. Microsoft's lookup guide says "the VLOOKUP function can only look up a value from left to right." For a lookup to the left, it suggests "the combination of INDEX and MATCH functions instead." MATCH finds the row number of the lookup value, and INDEX returns the value from that row in any column. The formula is longer, but it works in every version of Excel. For the price example, it would be =INDEX($H$2:$H$5,MATCH(B2,$F$2:$F$5,0)).
A VLOOKUP example
Here is a small office supply order. The product table sits in F1:H5, with headers in row 1.
| Product ID | Name | Price |
|---|---|---|
| P-101 | Notebook | 4.50 |
| P-102 | Pen | 1.20 |
| P-103 | Stapler | 8.75 |
| P-104 | Folder | 2.30 |
The order list sits in A1:C5. Each order has an ID, a product ID, and a quantity.
| Order | Product ID | Quantity | Price (VLOOKUP) | Line total |
|---|---|---|---|---|
| 1001 | P-103 | 2 | 8.75 | 17.50 |
| 1002 | P-101 | 10 | 4.50 | 45.00 |
| 1003 | P-104 | 5 | 2.30 | 11.50 |
| 1004 | P-105 | 1 | #N/A | — |
The formula in D2 is =VLOOKUP(B2,$F$2:$H$5,3,FALSE), filled down to D5. The line total in E2 is =C2*D2.
Order 1004 returns #N/A because P-105 is not in the product table. That is useful information, not a formula mistake. Someone needs to add the product or correct the code.
The three valid lines total 17.50 + 45.00 + 11.50, which is 74.00. Once P-105 is added to the table, its price appears automatically.
Do a VLOOKUP in Excel with AI
When you match large files with many columns, writing and checking lookups takes time. An AI tool can match the tables for you and flag rows that do not match.
Here is the method in three steps, using Powerdrill Bloom.
Step 1: Upload your Excel files
Upload both files, or one workbook with both tables. The Merge CSV File page says you can tailor a merge "according to specific column matches, sorting orders, or other criteria you define."
Make sure both tables share a key column, such as a product ID or employee ID.
Step 2: Ask for the match in natural language
Describe the lookup you need. For example: "Add the price from the product table to each order by product ID, and list any orders with no match."
For analysis after the match, the Excel AI Assistant "identifies trends, correlations, and key metrics in your Excel data automatically."
Step 3: Check the results and export them
Spot-check a few rows against the source table, especially any unmatched rows. Then download the combined file or ask for a summary report. A short list of unmatched IDs is often the most useful output, because it shows which records need fixing at the source.
For another way to combine files without formulas, see our guide on how to join two Excel files without VLOOKUP.
Common mistakes to avoid
These are the problems that come up most often with VLOOKUP.
- Leaving out the match type. The default is approximate. Type FALSE for IDs and codes.
- Relative table ranges. Without $ signs, the range shifts as you fill down.
- Counting columns from the sheet. Count from the first column of the range, not from column A.
- Lookup column on the right. VLOOKUP only looks to the right. Move the column or use XLOOKUP.
- Hidden spaces. "P-101 " with a trailing space will not match "P-101."
- Numbers stored as text. A code typed as text will not match the same code stored as a number.
- Duplicate keys. VLOOKUP returns the first match only, so duplicates hide later rows.
- Hiding every error. IFERROR is useful, but check what it hides before you share the file.
When you are ready to match and analyze large files without formulas, try Powerdrill Bloom with an order export and a product list.
Frequently asked questions
How do I do a VLOOKUP in Excel?
Type =VLOOKUP(lookup value, table range, column number, FALSE). For example, =VLOOKUP(B2,$F$2:$H$5,3,FALSE) finds B2 in the first column of F2:H5 and returns the third column.
Why does my VLOOKUP return #N/A?
With an exact match, #N/A means the value was not found. Check for typos, extra spaces, and numbers stored as text.
How do I do a VLOOKUP from another sheet?
Include the sheet name in the range, such as =VLOOKUP(A2,'Client Details'!A:F,3,FALSE). Clicking the other sheet while you build the formula adds the name for you.
What does FALSE mean in VLOOKUP?
FALSE asks for an exact match. TRUE, or leaving the argument out, asks for an approximate match and needs a sorted first column.
Should I use VLOOKUP or XLOOKUP?
Use XLOOKUP if everyone has Excel for Microsoft 365, Excel 2021, or later. It works in any direction and matches exactly by default. Use VLOOKUP when files go to people on Excel 2016 or Excel 2019.
Sources: Microsoft Support, VLOOKUP function · Microsoft Support, XLOOKUP function · Microsoft Support, Look up values with VLOOKUP, INDEX, or MATCH.