VLOOKUP searches down the first column of a table for a value and returns a matching value from a column you specify. It is the go-to function for pulling prices, names, grades, or any detail from a reference table without manual searching.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])What does VLOOKUP do?
VLOOKUP (Vertical Lookup) is one of Excel's most widely used Lookup & Reference functions. You give it a value to find, a table to search, and the column number that holds the answer. Excel scans the first column of the table from top to bottom, finds the first exact match (or the closest match if you allow it), and returns the value from the same row in your chosen column.
It matters because it replaces slow, error-prone manual searching. Instead of scrolling through hundreds of rows to find a product price or a student's grade, you type one formula and Excel does the work instantly. When the source data changes, the formula updates automatically, so your reports stay accurate.
Imagine you are an office worker with a price list of 500 products and an order sheet listing 50 product codes. You need the price for each code. With VLOOKUP you write one formula next to the first code, copy it down, and every price appears in seconds. Students can use the same technique to look up exam scores by student ID or course details by course code.
When to use VLOOKUP
- Pulling a product price from a catalogue using a product code
- Looking up a student's grade or score by student ID
- Finding an employee's department or manager from a staff list
- Matching a customer name to an account number in a CRM export
- Retrieving tax rates or shipping costs from a reference table
- Filling in missing details in a report from a master data sheet
VLOOKUP syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])| Argument | Required? | What to enter |
|---|---|---|
lookup_value | Required | The value you want to find in the first column of the table. It can be text, a number, a date, or a cell reference. Example: A2 or "P100". |
table_array | Required | The range containing your data. The first column must contain the lookup values. Example: A2:C10 or $A$2:$C$10. |
col_index_num | Required | The column number within table_array that holds the answer. Column 1 is the first column of the range. Example: 3 returns the third column. |
range_lookup | Optional | Use FALSE (or 0) for an exact match, which is what you want most of the time. Use TRUE (or 1) for an approximate match on sorted data. If omitted, Excel assumes TRUE. Example: FALSE. |
What it returns: VLOOKUP returns the value found in the specified column of the matching row. The result can be text, a number, a date, or an error value such as #N/A if no match is found.
VLOOKUP examples
1 Find a price by product code
You have a small price list and want the price for product code P003. The formula searches the first column (A) for "P003", finds it in row 4, and returns the value from column 3 of the range A2:C5, which is the Price column. The result is 15.99. Using FALSE forces an exact match so a similar code like P030 would not be returned by mistake.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Code | Product | Price | |||
| 2 | P001 | Pen | 1.5 | 15.99 | ||
| 3 | P002 | Notebook | 3.25 | |||
| 4 | P003 | Stapler | 15.99 | |||
| 5 | P004 | Folder | 2.1 | |||
| 6 |
2 Look up a student's grade by ID
A teacher has a grade table and needs the score for student ID 103. The formula looks down column A for 103, finds it in row 5, and returns the value from column 3 (Score) of the range A2:C7. The result is 88. Because the lookup value is a number, no quotation marks are used around it.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ID | Name | Score | |||
| 2 | 101 | Ava | 92 | 88 | ||
| 3 | 102 | Ben | 75 | |||
| 4 | 103 | Cara | 88 | |||
| 5 | 104 | Dan | 64 | |||
| 6 | 105 | Eve | 81 | |||
| 7 | 106 | Finn | 70 | |||
| 8 |
3 Return a department from an employee list
An HR assistant needs the department for employee ID E-204. The formula searches column A for "E-204", finds it in row 4, and returns the value from column 2 (Department) of the range A2:B6. The result is "Finance". This is a classic two-column lookup where the answer sits immediately to the right of the ID.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Employee ID | Department | |||
| 2 | E-201 | Sales | Finance | ||
| 3 | E-202 | Marketing | |||
| 4 | E-204 | Finance | |||
| 5 | E-205 | IT | |||
| 6 | E-206 | Operations | |||
| 7 |
4 Approximate match for a tax rate band
A payroll clerk needs the tax rate for a salary of 45000. The table lists salary thresholds in ascending order. Using TRUE (or 1) tells VLOOKUP to find the largest threshold that is less than or equal to 45000, which is 40000 in row 4, and return the rate from column 2: 0.22. Approximate match only works correctly when the first column is sorted ascending.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Min Salary | Tax Rate | |||
| 2 | 0 | 0.1 | 0.22 | ||
| 3 | 20000 | 0.15 | |||
| 4 | 40000 | 0.22 | |||
| 5 | 60000 | 0.3 | |||
| 6 | 80000 | 0.35 | |||
| 7 |
5 Realistic order sheet: pull price and calculate line total
An order sheet lists product codes and quantities. You want the unit price for code P1002. The formula searches the product table in A2:C6 for "P1002", finds it in row 4, and returns the price from column 3: 12.5. In a real sheet you would multiply this by the quantity to get a line total, but the VLOOKUP itself returns just the price.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Code | Product | Price | |||
| 2 | P1001 | Keyboard | 25 | 12.5 | ||
| 3 | P1002 | Mouse | 12.5 | |||
| 4 | P1003 | Monitor | 180 | |||
| 5 | P1004 | USB Cable | 4.99 | |||
| 6 | P1005 | Webcam | 45 | |||
| 7 |
Common VLOOKUP errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #N/A | The lookup value was not found in the first column of table_array. This often happens with extra spaces, different data types (text vs number), or a value that simply does not exist. | Check for leading or trailing spaces with TRIM, make sure both values are the same type, and confirm the value exists. You can wrap the formula in IFERROR to show a friendly message. |
| #REF! | The col_index_num is larger than the number of columns in table_array, or the table range has been deleted or moved. | Count the columns in your range and use a number within that count. If you deleted columns, undo or adjust the range. |
| #VALUE! | The col_index_num is less than 1 or not a number, for example a text value or a negative number. | Use a positive whole number that matches the column position you want, such as 2 or 3. |
| Wrong result (no error shown) | range_lookup was omitted or set to TRUE on unsorted data, so Excel returned an approximate match that looks plausible but is incorrect. | Always type FALSE (or 0) for exact matches unless you deliberately need a sorted approximate match. |
Tips for using VLOOKUP
- Use FALSE or 0 for the fourth argument in almost every case; exact match prevents silent wrong answers.
- Lock the table range with absolute references (for example
$A$2:$C$100) before copying the formula down. - Keep the lookup column as the first column of your range; VLOOKUP cannot look to the left.
- Wrap the formula in IFERROR to replace #N/A with a blank or a message like "Not found".
- For approximate matches, sort the first column in ascending order or the result will be unreliable.
- Use a named range or an Excel Table for the table_array to make formulas easier to read and maintain.
Frequently asked questions
Why does VLOOKUP return #N/A when the value is clearly there?
The most common reasons are hidden spaces, numbers stored as text, or a mismatch between the lookup value and the first column. Use TRIM and check the cell format. Also confirm you used FALSE for an exact match.
Can VLOOKUP look to the left?
No. VLOOKUP only searches the first column of table_array and returns values from columns to its right. If you need to look left, use INDEX and MATCH or XLOOKUP instead.
What is the difference between TRUE and FALSE in VLOOKUP?
FALSE means exact match: Excel returns a result only if it finds the exact value. TRUE means approximate match: Excel returns the closest value that is less than or equal to the lookup value, and the first column must be sorted ascending.
Does VLOOKUP work with dates?
Yes, as long as the dates are real Excel dates or consistently stored text. Use the same format in the lookup value and the first column, and use FALSE for an exact match.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. VLOOKUP reference