In Excel, VLOOKUP returns the value in a specified column that sits in the same row as the lookup value you searched for. For example, =VLOOKUP("A-100", A2:C10, 3, FALSE) finds “A-100” in column A and returns the matching value from column C. The function works by scanning the first column of a table for your lookup value, then pulling data from a column you choose to the right of it.
What VLOOKUP Actually Does
VLOOKUP stands for “vertical lookup.” It searches down the first column of a table (vertically) and returns a value from a cell in the same row, but from a column you specify. The syntax is:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Here is what each part means:
- lookup_value – the value you want to find (e.g., a product ID, name, or number).
- table_array – the range of cells that contains both the lookup column and the return column.
- col_index_num – the column number in the table from which to return a value. Column 1 is the first column of the table, column 2 is the second, and so on.
- range_lookup – TRUE for approximate match (needs sorted data) or FALSE for exact match. Most users should use FALSE.
For example, with product IDs in A2:A10 and prices in B2:B10, =VLOOKUP("P-203", A2:B10, 2, FALSE) returns the price for product P-203. If P-203 is in row 5, the formula returns the value from B5.
How to Build a VLOOKUP Formula Step by Step
- Click the cell where you want the result.
- Type
=VLOOKUP(. - Click the cell that contains the value you want to look up, or type it in quotes (e.g.,
"A-100"). - Select the table range that includes both the lookup column and the return column. Press F4 to make it absolute (e.g.,
$A$2:$C$10) so it doesn’t shift when you copy the formula. - Type the column number from which to return the value (count from the left of your selected range).
- Type
FALSEfor an exact match, then close the parenthesis and press Enter.
Example: If you have a table with employee IDs in A2:A20 and names in B2:B20, and you type an ID in D2, the formula =VLOOKUP(D2, $A$2:$B$20, 2, FALSE) returns the employee’s name.
Exact Match vs. Approximate Match
The fourth argument, range_lookup, controls how VLOOKUP finds your value:
- FALSE (exact match) – VLOOKUP looks for an exact match. If none is found, it returns
#N/A. This is the safest and most common choice. - TRUE (approximate match) – VLOOKUP finds the largest value in the first column that is less than or equal to your lookup value. The first column must be sorted in ascending order, otherwise results are unpredictable. This is useful for finding tax brackets, discount tiers, or shipping rates.
For example, with a rate table sorted by weight (A2:A6) and shipping cost (B2:B6), =VLOOKUP(3.2, A2:B6, 2, TRUE) returns the cost for the largest weight less than or equal to 3.2. If the table has 1, 2, 5, 10, and 20, it returns the cost for 2.
Common Reasons VLOOKUP Returns #N/A
Seeing #N/A is the most frequent problem. It means VLOOKUP couldn’t find a match. Common causes:
- No exact match exists – the value isn’t in the first column.
- Leading or trailing spaces – e.g., “A-100 ” vs. “A-100”. Use
=TRIM()on the lookup value or the data. - Numbers stored as text – the lookup value is a number but the table has text, or vice versa. Convert one to match the other.
- Incorrect column index – you may be pointing to a column that doesn’t exist in the table range.
- Table range doesn’t start at the lookup column – VLOOKUP always searches the first column of the table_array, so if you select B2:C10 but want to look up in A, it won’t work.
To fix, check the data types, trim spaces, and verify the table range starts with the column that contains your lookup values.
VLOOKUP vs. XLOOKUP: Which to Use?
XLOOKUP, available in Excel 2021 and Microsoft 365, is a modern replacement that fixes many VLOOKUP limitations:
- XLOOKUP can return a value from any column, even to the left of the lookup column.
- It defaults to an exact match, so you don’t need to specify FALSE.
- It has built-in handling for missing values (you can set a custom message).
- It can return multiple values with a single formula.
Example: =XLOOKUP("A-100", A2:A10, C2:C10) returns the value from C for the row where A equals “A-100”. If you have Microsoft 365 or Excel 2021, XLOOKUP is often easier and more robust. However, VLOOKUP still works in all versions and is widely used in older workbooks.
How to Return a Value from Another Sheet or Workbook
You can use VLOOKUP to pull data from a different sheet by including the sheet name in the table range:
=VLOOKUP(A2, 'Sheet2'!$A$2:$B$100, 2, FALSE)
For another workbook, include the workbook name in brackets: =VLOOKUP(A2, '[Sales.xlsx]Sheet1'!$A$2:$B$100, 2, FALSE). Make sure the other workbook is open, or use a full path if you want it to work when closed (though closed workbooks can be tricky).
Tips for Using VLOOKUP Effectively
- Always use
FALSEfor exact matches unless you have a sorted table and need approximate matching. - Use absolute references (e.g.,
$A$2:$B$10) so you can copy the formula down without shifting the table range. - Wrap VLOOKUP in
=IFERROR(VLOOKUP(...), "Not found")to display a friendly message instead of#N/A. - Keep your lookup column unique – duplicates cause VLOOKUP to return the first match only.
- For large datasets, consider using INDEX/MATCH or XLOOKUP for better performance and flexibility.
Frequently Asked Questions
Why does VLOOKUP return the wrong value?
The most common cause is using TRUE (approximate match) on an unsorted table. If the first column isn’t sorted ascending, VLOOKUP can return an incorrect but plausible value. Switch to FALSE for exact match, or sort the first column if you need approximate matching.
Can VLOOKUP return a value from a column to the left?
No, VLOOKUP can only return values from columns to the right of the lookup column. If you need to return a value from a column to the left, use INDEX/MATCH or XLOOKUP. For example, =INDEX(A2:A10, MATCH("X", C2:C10, 0)) returns a value from column A based on a match in column C.
How do I return multiple columns with VLOOKUP?
VLOOKUP returns only one value per formula. To return multiple columns, you can write separate VLOOKUP formulas for each column index, or use XLOOKUP with a range return. For instance, =XLOOKUP(A2, A2:A10, B2:D10) returns three values in adjacent cells (spill range) in Microsoft 365.
Does VLOOKUP work with wildcards?
Yes, when using FALSE, you can use wildcards in the lookup value: * (any sequence) and ? (single character). For example, =VLOOKUP("A*", A2:B10, 2, FALSE) returns the first value where column A starts with “A”. Note that this only works with text, not numbers.
Why does VLOOKUP return #REF!?
#REF! means the column index number is larger than the number of columns in the table range. For example, if your table is A2:B10 (2 columns) and you ask for column 3, you get #REF!. Check your col_index_num and the range size.
Can I use VLOOKUP to return a value based on a cell reference that changes?
Yes, you can use a cell reference as the lookup value. If you type an ID in D2 and use =VLOOKUP(D2, $A$2:$B$10, 2, FALSE), the formula updates when you change D2. This is the standard way to create a search box.
Related
- Excel Travel Expense Tracking Template: How to Build One
- Excel to Notepad Converter Online: How to Convert
- Excel Timesheet Template for Multiple Projects
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUMPRODUCT in Excel: Multiple Criteria ExplainedYes. In Excel, SUMPRODUCT can sum values that meet multiple criteria by multiplying TRUE/FALSE tests together; for example,…
- Excel Formula for Loan Payment Calculation: PMT ExplainedThe Excel formula for loan payment calculation is =PMT(rate, nper, pv), where rate is the periodic interest rate,…
- PySpark SQL split() Function: Usage & ExamplesThe split() function in PySpark SQL splits a string column into an array of substrings based on a…
- Excel Biweekly Timesheets: Setup and Formula GuideYes. Excel can track biweekly timesheets by recording each shift in a 14-day date range, calculating hours, and…
- JavaScript: How to Pass a String to a FunctionPass a string to a JavaScript function by placing it inside the function call’s parentheses: greet('Maya'). The function…
- Compare Two Sheets in Excel: Methods, Formulas, and TipsTo compare two Excel sheets, check corresponding cells with a formula such as =IF(January!A2=February!A2,"Match","Different"), then fill it across…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.