Contents
Ninth day of the marathon 30 Excel functions in 30 days will be devoted to the study of the function VLOOKUP (VPR). As you can guess from its name, this is one of the search functions that works with vertical lists.
Perhaps some functions will do a better job of extracting data from Excel tables (see VLOOKUP Pitfalls), but still VLOOKUP (VLOOKUP) is the first search function that users try for such tasks. Someone immediately puts it aside, others fight to make it work. Yes, it has a number of disadvantages, but once you understand how this feature works, you will be ready to try other options.
Well, let’s deal with the instructions for use VLOOKUP (VLOOKUP) and consider a few examples. If you have any tips or tricks for working with this feature, please share them in the comments. And don’t forget to keep your secrets!
Function 09: VLOOKUP
Function VLOOKUP (VLOOKUP) looks up a value in the first column of a table and returns another value from the same row in the table.
How can you use the VLOOKUP (VLOOKUP) function?
Since the function VLOOKUP (VLOOKUP) can find the exact match or nearest value in a column, it can:
- Find the price of the selected item.
- Convert student achievement, expressed as a percentage, to a letter grading system.
VLOOKUP Syntax
Function VLOOKUP (VLOOKUP) has the following syntax:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр)
- lookup_value (lookup_value): The value to be found. Can be a value or a cell reference.
- table_array (table): lookup table. Can be a range reference or a named range containing 2 or more columns.
- col_index_num (column_number): The column containing the value to be returned by the function. Set by the number of the column within the table.
- range_lookup (range_lookup): Use FALSE or 0 to find an exact match; for an approximate search, TRUE (TRUE) or 1. In the latter case, the column in which the function is searching must be sorted in ascending order.
Traps VLOOKUP (VLOOKUP)
Function VLOOKUP (VLOOKUP) can be slow, especially when searching for an exact match of a text string in an unsorted table. Whenever possible, use an approximate search in a table sorted by the first column in ascending order. You can first apply the function MATCH (MORE EXPOSED) or COUNTIF (COUNTIF) to make sure the value you’re looking for even exists in the first column (see example 3).
Other features like INDEX (INDEX) and MATCH (MATCH) can also be used to retrieve values from a table and are more efficient. We’ll take a look at them later in our marathon and see just how powerful and flexible they can be.
Example 1: Find the price for the selected item
Let me remind you again that the function VLOOKUP (VLOOKUP) only looks for a value in the left column of a table. In this example, we will find the price of the selected item. It is important to get the right price, so we use the following settings:
- The product name is entered in cell B7.
- The price lookup table has two columns and spans the range B3:C5.
- The price is recorded in column 2 of the table.
- The function’s last argument is set to FALSE to find an exact match when searching.
The formula in cell C7 is:
=VLOOKUP(B7,B3:C5,2,FALSE)
=ВПР(B7;B3:C5;2;ЛОЖЬ)
If the product name is not found in the first column of the table, then the function VLOOKUP (VLOOKUP) will return the result #AT (#N/A).
Example 2: Convert Percentages to Letter Grades
Usually when using the function VLOOKUP (VLOOKUP) look for an exact match, but sometimes an approximate match is better. For example, if you want to convert student achievement, expressed as a percentage, to a letter grade system. Luckily, you don’t have to write down all possible percentages in the spreadsheet, instead you only enter the lowest percentage for each letter, and use the function VLOOKUP (VLOOKUP) for approximate search. In this example:
- The percentage is recorded in cell C9.
- The percent lookup table has two columns and spans the range C3:D7.
- The lookup table is sorted in ascending order by the percentage column (1st column of the table).
- Letter designations of estimates are contained in the second column of the table.
- The function’s last argument is set to TRUE to perform an approximate lookup of values.
In cell D9, write the formula:
=VLOOKUP(C9,C3:D7,2,TRUE)
=ВПР(C9;C3:D7;2;ИСТИНА)
If the percentage is not found in the first column of the table, the function VLOOKUP (VLOOKUP) will find the nearest largest value that is less than the argument lookup_value (lookup_value). The desired value in our example is 77. It is not in the percentage column, so the formula will take the value 75 and return an estimate B.
Example 3: Find the exact price by an approximate match
Function VLOOKUP (VLOOKUP) can be slow when looking for an exact match of a text string. In the following example, we will find the price of the selected item without using exact match. To prevent errors:
- The table must be sorted by the first column in ascending order.
- Function COUNTIF (COUNTIF) will check for the presence of a value to prevent an error.
In cell C7, write the formula:
=IF(COUNTIF(B3:B5,B7),VLOOKUP(B7,B3:C5,2,TRUE),0)
=ЕСЛИ(СЧЁТЕСЛИ(B3:B5;B7);ВПР(B7;B3:C5;2;ИСТИНА);0)
If the product name is not found in the first column of the table, the function VLOOKUP (VLOOKUP) will return 0.
