Excel’s VLOOKUP function looks in the first column of an array and moves across the row to return the value of a cell.
VLOOKUP is the least-powerful and the most-restrictive lookup function that Excel offers. To learn about a few of its problems, see Why INDEX-MATCH Is Far Better Than VLOOKUP or HLOOKUP in Excel.
Syntax for Excel’s VLOOKUP Function
=VLOOKUP( lookup_value, table_array, col_index_num, [range_lookup] )
| lookup_value | Required. The value to search for in the first column of the table or range. This argument can be a value or a reference. If the value is smaller than the smallest value in the first column of the table_array argument, VLOOKUP returns the #N/A error value. |
| table_array | Required. The range of cells that contains the data. You can use a reference to a range (for example, B2:E8), or a range name. The values in the first column of table_array are the values in which VLOOKUP searches for the lookup_value. These values can be text, numbers, or logical values. Uppercase and lowercase text are equivalent. |
| col_index_num | Required. The column number in the table_array argument from which the matching value must be returned. A col_index_num argument of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. Excel returns an error value if the table_array argument is less than 1 or greater than the number of columns. |
| range_lookup |
Optional. A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If range_lookup is either TRUE or is omitted, an approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. Important: If range_lookup is either TRUE or is omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value. If range_lookup is FALSE, the values in the first column of table_array do not need to be sorted. If the range_lookup argument is FALSE, VLOOKUP will find only an exact match. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned. |
Example Formulas Using Excel’s VLOOKUP Function
You can download the example workbook here.
Some ExcelUser Articles about Excel’s LOOKUP Function
- XLOOKUP vs VLOOKUP vs INDEX-MATCH vs SUMIFS
- Use Excel’s INDEX-MATCH or VLOOKUP Functions to Populate Invoices and POs
- Why INDEX-MATCH Is Far Better Than VLOOKUP or HLOOKUP in Excel
- Excel’s VLOOKUP vs INDEX-MATCH Functions
More Information about Excel’s VLOOKUP Function
- VLOOKUP can return results from a closed external workbook.
- One of the major limitations of the VLOOKUP function is that its lookup_value must be the first column of its table_array. No other Excel lookup function has this restriction.
- Here’s the Microsoft help topic for the VLOOKUP function
| Related Functions | |
| XLOOKUP | Looks in the top row of an array and returns the value of the indicated cell. |
You can download the example workbook here.
