VLOOKUP function

VLOOKUP searches the first column of a table and returns a value from a specified column in the same row, optionally using exact or approximate matching.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Generate a VLOOKUP formula

Describe what you need. The generator will reach for VLOOKUP where VLOOKUP is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How VLOOKUP reads its arguments
lookup_valuerequiredtable_arrayrequiredcol_index_numrequiredrange_lookupoptionalVLOOKUP →
ArgumentRequiredDescription
lookup_valueRequiredThe value to search for in the first column of table_array; can be text, number, or date, and if not found VLOOKUP returns #N/A.
table_arrayRequiredA range of cells containing the data; the leftmost column is used for the search and must include the lookup_value type.
col_index_numRequiredA positive integer indicating which column of table_array to return; if less than 1 or greater than the table width, VLOOKUP throws #NUM! or #REF!.
range_lookupOptionalOptional logical flag; FALSE forces an exact match, TRUE (or omitted) allows an approximate match on sorted data.

Returns

It returns a single scalar value (text, number, or date) from the matched row.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Find the unit price for a specific order

Order IDRegionRepUnitsUnit PriceOrder Date
1001EastAlice5202023-01-15
1002WestBob3152023-01-17
1003EastCarol8222023-01-20
1004SouthDave2302023-01-22
1005WestEve7182023-01-25
=VLOOKUP(1003, A2:F6, 5, FALSE)

Result: 22

The formula looks for the numeric ID 1003 in column A. Because we set range_lookup to FALSE, VLOOKUP requires an exact match, which it finds on the third data row. Column 5 of the table (Unit Price) contains the value 22, so that is returned.

2. Calculate total revenue for an order by nesting VLOOKUP

Order IDRegionRepUnitsUnit PriceOrder Date
1001EastAlice5202023-01-15
1002WestBob3152023-01-17
1003EastCarol8222023-01-20
1004SouthDave2302023-01-22
1005WestEve7182023-01-25
=VLOOKUP(1001, A2:F6, 4, FALSE) * VLOOKUP(1001, A2:F6, 5, FALSE)

Result: 100

The first VLOOKUP pulls the Units (5) for order 1001, and the second pulls the Unit Price (20). Multiplying them yields the total revenue of 100. Both lookups use FALSE for exact matching, ensuring the correct row is used.

3. Retrieve the order date for a given ID (date return type)

Order IDRegionRepUnitsUnit PriceOrder Date
1001EastAlice5202023-01-15
1002WestBob3152023-01-17
1003EastCarol8222023-01-20
1004SouthDave2302023-01-22
1005WestEve7182023-01-25
=VLOOKUP(1002, A2:F6, 6, FALSE)

Result: 2023-01-17

VLOOKUP searches for the ID 1002 in the first column and returns the value from column 6, which stores the Order Date. Because dates are stored as serial numbers, the cell displays the human-readable date "2023-01-17".

Common errors

Which VLOOKUP error are you seeing?
VLOOKUP returned an error#N/A
Verify the ID exists or wrap the call in IFERROR to supply a fallback value.
#REF!
Adjust col_index_num to a value that is within the table width, e.g., change 7 to 6 for a six-column table.
#NUM!
Use a positive integer such as 1 or higher that corresponds to an existing column.
ErrorWhy it happensHow to fix it
#N/AThe lookup_value (e.g., an order ID that does not exist) cannot be found in the first column of table_array.Verify the ID exists or wrap the call in IFERROR to supply a fallback value.
#REF!col_index_num is larger than the number of columns in table_array, so VLOOKUP cannot locate the return column.Adjust col_index_num to a value that is within the table width, e.g., change 7 to 6 for a six-column table.
#NUM!col_index_num is less than 1 (zero or negative), which is not a valid column position.Use a positive integer such as 1 or higher that corresponds to an existing column.

Tips and when to use something else

  • Always set range_lookup to FALSE for exact matches unless your data is sorted and you need the nearest lower value.
  • If you need to look left of the lookup column, VLOOKUP cannot do it; switch to INDEX/MATCH or XLOOKUP instead.
  • Remember that VLOOKUP returns the first match it encounters; duplicate keys will always yield the same row.
  • Large tables benefit from converting the range to an Excel Table (Ctrl+T) so column numbers stay stable when rows are added.

Related functions

XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.MATCHMATCH returns the relative position of a lookup value within a one-dimensional range, optionally using exact, less-than, or greater-than matching.

Frequently asked questions

Why does VLOOKUP sometimes return the wrong value when I omit the fourth argument?
When the fourth argument (range_lookup) is omitted, VLOOKUP assumes TRUE, meaning it performs an approximate match on a sorted column. If the first column isn’t sorted ascending, the function may return the nearest lower value instead of an exact match. Always specify FALSE for exact lookups unless you intentionally need the approximate behavior.
Can VLOOKUP retrieve values from columns to the left of the lookup column?
No. VLOOKUP only searches vertically in the first column of the range and returns values from columns to the right. To look left, use a combination of INDEX and MATCH or the newer XLOOKUP, which allows reverse lookups.
How does VLOOKUP handle text that looks like numbers, such as "00123"?
VLOOKUP treats the lookup_value’s data type strictly. If the first column stores numbers (123) but you search for a text string "00123", the function will not find a match and return #N/A. Ensure both sides share the same type, or coerce using VALUE or TEXT functions.
Is VLOOKUP case-sensitive when matching text?
VLOOKUP performs a case-insensitive comparison, so "Alice" and "alice" are considered equal. If you need case-sensitive lookup, combine EXACT with FILTER or use XLOOKUP with the match_mode argument set to 2.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator