Looks up a value in the first column of a range and returns a value from a column to its right in the same row.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])lookup_value - The value to search for in the first column of the range.table_array - The range that contains the data. The lookup happens in its first column.col_index_num - The column number in the range (starting at 1) whose value to return.range_lookup (optional) - FALSE for an exact match (recommended), TRUE for an approximate match on sorted data.=VLOOKUP("A1", D2:F10, 3, FALSE)Returns: The value in column F of the row whose column D equals "A1"
Exact match. The 3 means return the third column of D:F, which is F.
=VLOOKUP(A2, $D$2:$F$100, 2, FALSE)Returns: Column E value matching A2
Absolute references ($) keep the table fixed when you copy the formula down.
VLOOKUP scans down the first column of table_array looking for lookup_value, stops at the first row that matches, and returns whatever sits in column col_index_num of that row. Everything about it follows from those two facts: the search column is always the leftmost column of the range you hand it, and col_index_num is counted from that column, not from column A of the sheet. Move the range one column left and every col_index_num in the workbook shifts meaning.
The fourth argument decides which of two entirely different algorithms runs. FALSE (or 0) does a linear scan for an equal value and returns #N/A when there is none. TRUE, or leaving the argument out, does a binary-style search for the largest value less than or equal to the lookup value, which is only correct when the first column is sorted ascending. On unsorted data an approximate match does not error - it returns a confidently wrong row, which is why omitting the argument is the single most expensive habit in the function.
VLOOKUP cannot look left, cannot return more than one column at a time without help, and re-reads the lookup column for every formula you copy down. INDEX/MATCH removes the direction limit, and XLOOKUP removes both the direction limit and the dangerous default. VLOOKUP remains worth knowing because it is in every workbook written before 2021 and it is the shortest thing to type when the layout suits it.
| Error | What it means and how to fix it |
|---|---|
| #N/A | No row in the first column equals the lookup value. Usual causes are trailing spaces (test with =LEN(A2) against the source cell), a number stored as text on one side, or a lookup range that starts below the row you need. TRIM the keys or fix their type; do not paper over it with IFERROR until you know the value really is absent. |
| #REF! | col_index_num is larger than the number of columns in table_array - asking for column 4 of a three-column range. It also appears after deleting a column that the range covered. Recount the columns from the left edge of table_array. |
| #VALUE! | col_index_num is less than 1, or is text that cannot be read as a number. It also shows when table_array is a single value rather than a range. |
| #NAME? | The function name is misspelled, or quotes are missing around a text lookup value so Excel reads it as an undefined name. |
| Wrong value, no error | range_lookup was omitted or TRUE on unsorted data. VLOOKUP returned the nearest smaller key it found before giving up. Add FALSE, or sort the first column ascending if you genuinely want banded lookup. |
=VLOOKUP(TRIM(A2), Products!$A$2:$D$500, 4, FALSE)Returns: Column D of the matching product row
TRIM on the lookup value absorbs the trailing spaces that pasted keys usually carry. The sheet name makes the table explicit so the formula survives being copied to another tab.
=VLOOKUP(B2, $A$2:$C$8, 3, TRUE)Returns: The band the score in B2 falls into
The deliberate use of approximate match: A2:A8 holds ascending band floors (0, 60, 70, 80, 90) and C holds the label. Any score picks the last floor it is at or above.
=VLOOKUP($A2, $D$2:$H$200, COLUMNS($D$2:E$2), FALSE)Returns: The next table column each time you copy right
COLUMNS grows from 2 to 3 to 4 as the formula is dragged across, so one formula fills a whole block without hand-editing col_index_num.
| Application | Support |
|---|---|
| Excel 2007-2016 | Fully supported, all four arguments. Wildcards work in exact mode. |
| Excel 2019 | Identical. XLOOKUP is not available here, so VLOOKUP and INDEX/MATCH are the options. |
| Excel 2021 / Microsoft 365 | Supported and unchanged, but XLOOKUP is present and is the better default for new formulas. |
| Google Sheets | Same syntax and same defaults. Sheets also accepts a search key expressed as a regular expression only if you switch it on in the file's calculation settings. |
| LibreOffice Calc | Supported. Wildcard behaviour depends on Tools, Options, Calc, Calculate, where you choose wildcards, regular expressions, or neither. |
Yes. VLOOKUP uses the same syntax in Google Sheets and LibreOffice Calc, so you can paste these formulas straight across.
The ExcelTool editor implements VLOOKUP with three or four arguments and matches Excel's default: leave range_lookup out and the lookup is approximate, exactly as in Excel.
Exact match is case-insensitive, and a lookup value containing * or ? is treated as a wildcard pattern with ~ as the escape character. Approximate match skips blank keys and stops at the first key greater than the lookup value, so the first column must be sorted ascending.
A col_index_num below 1 returns #VALUE! and one wider than the table returns #REF!. Where Excel turns a matched-but-empty return cell into 0, the editor leaves it blank.
You can try any of these formulas in the free in-browser spreadsheet editor - it opens .xlsx files, recalculates as you type, and needs no signup.
Fill in your ranges and copy a ready-to-paste formula - no signup.
Almost always a mismatch you cannot see: a trailing space, a non-breaking space pasted from a web page, or a number stored as text against a real number. Compare =LEN(A2) with =LEN(D5) on the two cells - different lengths means invisible characters. =ISNUMBER(A2) against =ISNUMBER(D5) catches the type mismatch. Fix the source column with TRIM or by re-entering the values as numbers rather than wrapping every formula in IFERROR.
No. The search always runs in the first column of table_array and col_index_num only counts rightwards, so a negative or zero index gives #VALUE! rather than looking left. Use =INDEX(returnRange, MATCH(key, keyRange, 0)), which places no constraint on the order of the two columns, or XLOOKUP in Excel 2021 and later.
Leaving it blank is the same as TRUE, which is approximate match. VLOOKUP then assumes the first column is sorted ascending and returns the row holding the largest key that is not greater than your lookup value. On unsorted data that produces a plausible but wrong answer with no error, so type FALSE unless you are deliberately doing a banded lookup like a tax table or grade scale.
An exact-match VLOOKUP scans the lookup column row by row, so ten thousand formulas over a hundred-thousand-row table is a lot of repeated scanning, especially if table_array is a whole-column reference like A:D. Limit the range to the rows that exist, or compute MATCH once in a helper column and feed that position into INDEX, which turns a scan per formula into one scan per row.