Excel VLOOKUP Function
last modified October 5, 2026
The VLOOKUP function is one of Excel's most powerful lookup and
reference functions. It searches vertically down the first column of a range for
a key and returns a value from a column to the right. This tutorial provides a
comprehensive guide to using VLOOKUP with detailed examples. You'll
learn the basic syntax, the difference between exact and approximate matching,
wildcard searches, the left-lookup limitation, and how VLOOKUP
compares with INDEX-MATCH and XLOOKUP.
VLOOKUP Function Basics
The VLOOKUP function searches for a value in the first column of a
table array and returns a value in the same row from another column. It is
essential for data retrieval tasks in Excel.
| Component | Description |
|---|---|
| Function Name | VLOOKUP |
| Syntax | =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) |
| Arguments | 4 parameters (last optional) |
| Return Value | Matched value from table array |
This table breaks down the essential components of the VLOOKUP
function. It shows the function name, syntax format, argument details, and
return value characteristics.
The four arguments control what is searched, where it is searched, which column
is returned, and how strict the match must be. The lookup value must always live
in the first column of table_array.
| Argument | Meaning |
|---|---|
| lookup_value | The value to find in the first column of the table. It can be a number, text, a logical value, or a cell reference. |
| table_array | The range or array that holds the data. VLOOKUP always
searches the first column of this range and reads results from the columns
to its right. |
| col_index_num | The column within table_array to return, counted from 1.
1 returns the first column, 2 the second, and so
on. A value below 1 gives #VALUE!; a value larger than the
number of columns gives #REF!. |
| range_lookup | Optional. FALSE (or 0) requests an exact match,
TRUE (or 1) or omitted requests an approximate match. |
The key point is direction. VLOOKUP searches down the first column
and returns a value from a column to the right, while HLOOKUP
searches across the first row. Use VLOOKUP when your comparison
values run vertically down the left edge of a table.
Exact Match vs Approximate Match
The range_lookup argument decides how VLOOKUP treats a
miss. The two modes behave very differently.
| range_lookup | Match type | Behaviour |
|---|---|---|
| FALSE | Exact match | Returns the row whose first-column entry equals the lookup value. If
none is found, returns #N/A. Text comparison is
case-insensitive, and wildcards are allowed. |
| TRUE or omitted | Approximate match | Returns the value for the largest first-column entry that is less than or equal to the lookup value. The first column must be sorted in ascending order, otherwise the result may be wrong. |
With approximate matching, if the lookup value is smaller than the smallest
entry in the first column, VLOOKUP returns #N/A.
Because omitting the argument triggers approximate matching, always pass
FALSE when you need a reliable exact lookup on unsorted data.
A frequent surprise is a type mismatch. Numbers and text that merely look alike
are not equal, so looking up the number 100 in a column of text
values "100" (or the reverse) returns #N/A even
though the values appear identical on screen.
Basic VLOOKUP Example
This example demonstrates the simplest use of VLOOKUP to find product prices from a product ID.
| A | B | C |
|---|---|---|
| Product ID | Product Name | Price |
| P100 | Laptop | 999 |
| P101 | Mouse | 25 |
| P102 | Keyboard | 45 |
| =VLOOKUP("P101", A2:C4, 3, FALSE) |
The table shows a product list with IDs, names, and prices. The VLOOKUP formula searches for product ID "P101" and returns its price from column 3.
=VLOOKUP("P101", A2:C4, 3, FALSE)
This formula searches for "P101" in the first column of A2:C4. When found, it returns the value from the 3rd column of the same row. The FALSE parameter ensures an exact match. The result is 25.
VLOOKUP with Cell Reference
A more practical use involves referencing a cell for the lookup value instead of hardcoding it. This makes the formula dynamic.
| A | B | C | D |
|---|---|---|---|
| Product ID | Product Name | Price | Search ID |
| P100 | Laptop | 999 | P102 |
| P101 | Mouse | 25 | |
| P102 | Keyboard | 45 | =VLOOKUP(D2, A2:C4, 3, FALSE) |
This table demonstrates using a cell reference (D2) for the lookup value. The formula in D4 will return the price for whatever product ID is entered in D2.
=VLOOKUP(D2, A2:C4, 3, FALSE)
This formula looks up the value in D2 ("P102") within A2:C4 and returns the
price from column 3. The result is 45. Changing D2 updates the result
automatically. Using an absolute range such as $A$2:$C$4 lets you
fill the formula down without shifting the table reference.
VLOOKUP with Approximate Match
VLOOKUP can perform approximate matches when the last parameter is TRUE or omitted. This is useful for finding category ranges like tax brackets.
| A | B | C |
|---|---|---|
| Income | Tax Rate | Lookup Income |
| 0 | 10% | 42000 |
| 20000 | 15% | |
| 40000 | 22% | =VLOOKUP(C2, A2:B4, 2, TRUE) |
The table shows tax brackets with income thresholds and corresponding rates. The VLOOKUP finds the appropriate tax rate for the income in C2 (42000).
=VLOOKUP(C2, A2:B4, 2, TRUE)
This formula finds the largest value in column A that is less than or equal to
42000 (40000) and returns the corresponding tax rate (22%). The table must be
sorted in ascending order for this to work correctly. Looking up an income below
0 would return #N/A because no threshold is small enough.
VLOOKUP with Wildcards
VLOOKUP supports wildcards (* and ?) for partial matches when doing exact lookups (FALSE as last parameter). This helps find values when you know only part of the lookup value. An asterisk matches any sequence of characters, and a question mark matches any single character.
| A | B | C |
|---|---|---|
| Product Name | Price | Search Term |
| Wireless Mouse | 25 | *Mouse |
| Bluetooth Keyboard | 45 | |
| USB Cable | 10 | =VLOOKUP(C2, A2:B4, 2, FALSE) |
The table demonstrates using wildcards in VLOOKUP. The formula searches for any product ending with "Mouse" and returns its price.
=VLOOKUP(C2, A2:B4, 2, FALSE)
This formula searches for any value in column A that ends with "Mouse" (as
specified by "*Mouse" in C2) and returns the corresponding price. The result is
25. To find a literal asterisk or question mark, escape it with a tilde, for
example "~*". Wildcards work only with exact matching; they are
ignored when range_lookup is TRUE.
VLOOKUP with Multiple Criteria
While VLOOKUP normally handles single criteria, you can combine it with helper columns to handle multiple criteria lookups.
| A | B | C | D | E |
|---|---|---|---|---|
| Region | Product | Combined | Sales | Search |
| East | Widget | EastWidget | 1500 | WestGadget |
| West | Gadget | WestGadget | 2200 | |
| North | Tool | NorthTool | 1800 | =VLOOKUP(E2, C2:D4, 2, FALSE) |
The table shows sales data by region and product. Column C combines region and product to create a unique lookup key. The formula finds sales for West Gadget.
=VLOOKUP(E2, C2:D4, 2, FALSE)
This formula searches for "WestGadget" in the combined key column (C) and returns the corresponding sales from column D. The result is 2200. Helper columns enable multi-criteria lookups with VLOOKUP.
VLOOKUP Cannot Look Left
VLOOKUP searches the first column of table_array and
returns a value from a column to the right. It can never return a value to the
left of the key column, because that key must be the first column of the range.
| A | B |
|---|---|
| Name | ID |
| John | 101 |
| Sarah | 102 |
| Mike | 103 |
Suppose the table above lists names in column A and IDs in column B, and you
want the name for ID 102. VLOOKUP fails here because the IDs are to
the right of the names, so the key is not in the first column. The
INDEX-MATCH combination solves this:
=INDEX(A2:A4, MATCH(102, B2:B4, 0)) returns "Sarah".
=INDEX(A2:A4, MATCH(102, B2:B4, 0))
MATCH finds 102 in B2:B4 (position 2), and INDEX
returns the corresponding name from A2:A4. Result is "Sarah". This is why
INDEX-MATCH is preferred when the return column sits to the left of
the lookup column.
VLOOKUP vs INDEX-MATCH vs XLOOKUP
All three tools can look up data, but they differ in direction, safety, and availability. The table below summarizes the trade-offs.
| Feature | VLOOKUP | INDEX-MATCH | XLOOKUP |
|---|---|---|---|
| Lookup direction | Right only | Any direction | Any direction |
| Safe when columns are inserted | No, the column index breaks | Yes | Yes |
| Exact match by default | No, pass FALSE | No, pass 0 | Yes |
| Not-found handling | #N/A, wrap in IFERROR | #N/A, wrap in IFNA | if_not_found argument |
| Availability | All versions | All versions | Excel 2021 and 365 |
For example, the same result as
=VLOOKUP("P101", A2:C4, 3, FALSE) can be written as
=INDEX(C2:C4, MATCH("P101", A2:A4, 0)) or
=XLOOKUP("P101", A2:A4, C2:C4). XLOOKUP is the most
convenient because it returns an exact match by default, can look in any
direction, and has a built-in argument for values that are not found. It is
available in Microsoft 365 and Excel 2021 and later.
VLOOKUP Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| Lookup value does not exist in the first column | #N/A error | Check spelling, use wildcards for partial text, or wrap in
IFERROR |
| Approximate match on unsorted data | A wrong but plausible value | Sort the first column ascending, or use FALSE for an exact
match |
| Lookup value is smaller than the smallest first-column entry | #N/A error with approximate match | Confirm the first column is sorted and the value is within range |
| Numbers stored as text (or the reverse) | #N/A even though the value looks present | Make both sides the same type, for example with VALUE |
| col_index_num is less than 1 | #VALUE! error | Use a column number of 1 or greater |
| col_index_num is greater than the number of columns | #REF! error | Make sure the index does not exceed the width of
table_array |
| Wildcard characters used with approximate match | Literal match fails or wrong value | Wildcards only work when range_lookup is
FALSE |
The VLOOKUP function is indispensable for Excel users who need to
retrieve data from tables. From simple exact matches to approximate range
lookups and wildcard searches, it handles many common data tasks. Remember that
it searches only the first column of the range, that exact matching is
case-insensitive and supports wildcards, and that approximate matching demands a
sorted first column. Watch out for numbers stored as text, and reach for
INDEX-MATCH when you must look to the left or when inserted columns
must not break your formula. Mastering VLOOKUP, and knowing when to
move beyond it, will make your spreadsheets more reliable and your lookups far
more flexible.
Author
List all Excel Formulas.