VLOOKUP()
Looks up a value in the first column of a range and returns a value from another column in the same row.
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])VLOOKUP searches down the leftmost column of a range for a value, then returns a value from a column you specify, counting from the left edge of that range.
Set range_lookup to FALSE for an exact match, which is almost always what you want. Leaving it blank or setting it to TRUE does an approximate match, which requires the lookup column to be sorted ascending.
The biggest limitation: VLOOKUP can only look to the right of the lookup column, and it breaks if you insert a new column inside the table_array. XLOOKUP and INDEX/MATCH avoid both problems.
Arguments
- lookup_value
- The value to search for.
- table_array
- The range to search — the lookup column must be its first (leftmost) column.
- col_index_num
- Which column to return from, counting from 1 at the left edge of table_array.
- range_lookupoptional
- FALSE for an exact match; TRUE or omitted for an approximate match on sorted data.
Examples
=VLOOKUP(D2, A:B, 2, FALSE)Finds the product typed in D2 in column A, returns its price from column B.
=VLOOKUP("SKU-102", A2:C500, 3, FALSE)Returns #N/A when the lookup value isn't found in the first column of the range.