FormuladrillVLOOKUP
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) searches the first column of a range for a value and returns the value in the same row from a column you number. Use FALSE as the last argument for an exact match; TRUE (the default) is for sorted ranges and bracket lookups.
Syntax
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])| Part | What it is |
|---|---|
lookup_value | The value to find in the first column of table_array. |
table_array | The range to search. The lookup column must be its first column. |
col_index_num | Which column of table_array to return, counted from 1 at the left. |
range_lookup | FALSE for exact match. TRUE or omitted for approximate match on a sorted first column. |
Examples
=VLOOKUP("P-102", A2:C50, 3, FALSE)Result: The value in column C on the row where column A equals P-102
Exact match. If P-102 is not in A2:A50 the result is #N/A.
=VLOOKUP(E2, Products!A:D, 2, 0)Result: Column B of the Products sheet for the code typed in E2
0 is the same as FALSE. Whole-column references are fine for exact matches.
=VLOOKUP(45000, A2:B6, 2, TRUE)Result: The rate next to 40000 when A2:A6 holds 0, 10000, 20000, 40000, 80000
Approximate match returns the largest value that is less than or equal to the lookup value. The first column must be sorted ascending.
=IFERROR(VLOOKUP(E2, A:C, 3, FALSE), "Not found")Result: Not found (instead of #N/A) when E2 is missing from column A
Common mistakes
- The formula returns a wrong row when you omit the fourth argument: Excel then does an approximate match, so add FALSE for exact matches.
- #N/A even though the value is there: one side is a number and the other is text, or the lookup value has a trailing space. Fix with VALUE or TRIM.
- Results shift after inserting a column inside the table: col_index_num is a fixed number, so update it or switch to XLOOKUP or INDEX/MATCH.
- #REF! means col_index_num is larger than the number of columns in table_array.
Practice VLOOKUP
These drills check your answer on live data, free, no account needed.
- Find the priceLook up the price of the product code in F1 using the table in A:C. Put the price in F2 and require an exact match.
- Tax bracket lookupFind each employee's tax rate from the bracket table in E:F (each row starts at the income in column E). Fill C2 and C3.
- Price list into an orderEach order line has a product code and a quantity. Look up the unit price in the price list in G:H and multiply by the quantity. Fill D2 and D3.
Questions
Can VLOOKUP look to the left?
No. VLOOKUP only searches the first column of the range and returns from columns to its right. Use XLOOKUP or INDEX/MATCH to return a value from a column on the left.
VLOOKUP or XLOOKUP, which should I use?
XLOOKUP if your Excel has it (Excel 2021, Microsoft 365). It looks in any direction, defaults to exact match and has a built-in not-found value. VLOOKUP still matters because it is everywhere in existing files.
Why does VLOOKUP return #N/A?
The lookup value was not found in the first column with an exact match, or with approximate match the value is smaller than the first entry. Check for stray spaces, number-stored-as-text and that the lookup column really is the first column of the range.
Related
- XLOOKUPHow XLOOKUP works in Excel: find a value in one column and return the match from another, in any direction, with a not found value and next-smaller match.
- INDEX MATCHHow to combine INDEX and MATCH in Excel to look up a value from any column, left or right, with worked examples, a two-way lookup and the usual errors.
- IFERRORHow the Excel IFERROR function returns a value you choose instead of an error, with examples for division by zero and VLOOKUP, and when IFNA is better.
- MATCHHow the Excel MATCH function returns the position of a value in a row or column, with exact and approximate match types, examples and the causes of #N/A.