- In large Excel spreadsheets, VLOOKUP can help you find data
- How the VLOOKUP function works
- Function Arguments and VLOOKUP Syntax
- How the Range_Lookup Argument Works
- VLOOKUP errors: #N/A and #REF
In large Excel spreadsheets, VLOOKUP can help you find data
The Excel VLOOKUP function, which stands for “vertical lookup”, searches for a value in the first column of a range and returns the value in another column in the same row.
If you can’t determine which cell contains certain data, VLOOKUP is a very efficient way to find that data. This is especially useful in giant spreadsheets where information is hard to find.
The instructions in this article apply to Excel 2016, 2013, 2010; Excel for Mac and Excel for 365/Web.
How the VLOOKUP function works
VLOOKUP usually returns one data field as output.
How it works:
You enter a name or lookup value that tell VLOOKUP in which row of the data table the desired data is to be found.
The column number is given as an argument col_index_num which tells VLOOKUP which column contains the data to look up.
The position is looking for lookup value in the first column of the datasheet.
VLOOKUP then finds and returns information from the column number you defined in col_index_num of the same string as the lookup value.
Function Arguments and VLOOKUP Syntax
The syntax for the VLOOKUP function is:
= ВПР (искомое_значение, таблица_массив, номер_столбец, интервальный_просмотр)
The VLOOKUP function may seem confusing because it takes four arguments, but it’s easy to use.
Here are the four arguments to the VLOOKUP function:
lookup_value (required) : value to look up in the first column of the table array.
table_array (required) is the data table (range of cells) that VLOOKUP searches to find the required information.
table_array must contain at least two columns of data
The first column must contain lookup_value
col_index_num (mandatory) . This is the column number of the value you are looking for.
Numbering starts from column 1
If you reference a number greater than the number of columns in the table array, the #REF! error
range_lookup (optional) . Indicates whether the lookup value is within the scope of the table array. The range_lookup argument is TRUE or FALSE. Use TRUE for an approximate match and FALSE for an exact match. If omitted, the default value is TRUE.
If the range_lookup argument is TRUE, then:
Lookup_value is the value you want to check if it falls within the range defined by table_array.
The table_array argument contains all ranges and a column with the value of the range (for example, high, medium, or low).
The col_index_num argument is the resulting range value.
How the Range_Lookup Argument Works
Using an optional argument range_lookup hard to understand for many people, so it’s worth looking at a quick example.
In the example in the image above, the VLOOKUP function is used to find the discount percentage based on the number of items purchased.
The example shows that the discount for buying 19 items is 2% because 19 is in the column between 11 and 21 Quantity lookup tables.
As a result, VLOOKUP returns the value from the second column of the lookup table because that row contains the minimum of that range. Another way to set up a range lookup table is to create a second column for the maximum and that range has a minimum of 11 and a maximum of 20. But the result works the same way.
This example uses the following formula, which includes the VLOOKUP function, to find a discount on the number of items purchased.
= VLOOKUP(C2, $C$5:$D$8.2, TRUE),
- C2 . This is a lookup value that can be in any cell in the worksheet.
- $C$5:$D$8 . This is a fixed table with all the ranges you want to use.
- 2 . This is the column in the range lookup table to be returned by the LOOKUP function.
- WHERE . Enables the feature: range_lookup this function.
After you click . has printed Input and the result will return to the first cell, you can automatically fill the whole column to see the range results for the rest of the cells in the search column.
The range_lookup argument is a compelling way to sort a column of mixed numbers into different categories.
VLOOKUP errors: #N/A and #REF
The VLOOKUP function may return the following errors.
# N/A is a “value not available” error that occurs under the following conditions:
- lookup value not found in first column of table_array argument
Argument table_array is incorrect. For example, the argument can contain empty columns to the left of the range
for argument Range_lookup set to FALSE and an exact match to the lookup_value argument cannot be found in the first column table_array .
Argument range_lookup is TRUE and all values in the first column of table_array are greater than lookup_value
Error #ref! (“Reference out of range”) occurs if the col_index_num column exceeds the number of columns in the table.