vlookup

VLOOKUP is a widely used function in spreadsheet software for retrieving data from a table by matching a value in the first column. It is a standard feature in Microsoft Excel and Google Sheets, and is commonly taught as a basic data retrieval tool.

Syntax: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). The function searches for lookup_value in the leftmost column of table_array. If range_lookup is omitted or set to TRUE, VLOOKUP uses an approximate match and requires the first column to be sorted in ascending order. If range_lookup is FALSE, VLOOKUP performs an exact match. The function returns the value in the row where the match is found from the column specified by col_index_num. If no match is found, the function returns the error value #N/A.

Notes: The value returned comes from a column to the right of the lookup column; col_index_num must be between 1 and the number of columns in table_array. VLOOKUP is not case sensitive.

Limitations and alternatives: VLOOKUP can be slow on very large datasets and cannot look to the left of the lookup column. It also requires the lookup column to be the first column in the table_array. Alternatives include INDEX/MATCH for more flexible lookups and XLOOKUP in Excel, which can replace many VLOOKUP use cases with improved capabilities and behavior.

Common uses include retrieving prices, names, or statuses from a table by supplying a key such as a product code or ID.