Contents
Function VLOOKUP (VLOOKUP) is one of the most popular functions in the category References and arrays in Excel. And also this is one of the most complex Excel functions, where a terrible mistake #AT (#N/A) may become a familiar pattern. In this article, we will look at the 6 most common reasons why a function VPR does not work.
You need an exact match
Last function argument VPR, known as range_lookup (range_lookup), asks if you want to get an approximate or exact match.
In most cases, people are looking for a specific product, order, employee, or customer and therefore want an exact match. If you are looking for a unique value, then the argument range_lookup (range_lookup) should be FALSE (FALSE).
This argument is optional, but if it is not specified, the value will be used. TRUE CODE (TRUE). In this case, for the function to work correctly, the data must be sorted in ascending order.
The figure below shows the function VPR with missing argument range_lookup (range_lookup), which returns an erroneous result.
Solution
If you are looking for a unique value, set the last argument to FALSE (FALSE). Function VPR the example above should look like this:
=VLOOKUP(H3,B3:F11,2,FALSE)
=ВПР(H3;B3:F11;2;ЛОЖЬ)
Fix table links
You may want to use several functions at once VPRto extract more information. If you are going to copy a function VPR into several cells, it is necessary to fix some of the arguments.
The figure below shows an example of the function VPRentered incorrectly. For arguments lookup_value (lookup_value) and table_array (table) incorrect cell ranges entered.
Solution
Argument table_array (table) is the table that VPR uses to find and retrieve information. To correctly copy a function VPR, in the argument table_array (table) must be an absolute reference to a range of cells.
Click on the link address inside the formula and press F4 on the keyboard to turn the relative link into an absolute one. The formula should be written like this:
=VLOOKUP($H$3,$B$3:$F$11,4,FALSE)
=ВПР(($H$3;$B$3:$F$11;4;ЛОЖЬ)
In this example references in arguments lookup_value (lookup_value) and table_array (table) are made absolute. Sometimes it is enough to fix just the argument table_array (table).
Column inserted
Argument col_index_num (column_number) is used by the function VPRto specify what information to extract from the entry.
Because the argument is entered as a numeric index, it is not very reliable. If a new column is inserted into the table, the function VPR may stop working. The figure below shows just such a scenario.
Column Quantity (number) was 3-m by count, but after adding a new column it became 4-m. However, the function VPR not automatically updated.
Solution 1
One solution would be to secure the table so that users cannot insert new columns. In the event that users require such an opportunity, the solution will not be viable.
Solution 2
Another option is to insert a function MATCH (MATCH) to argument col_index_num (column_number) functions VPR.
Function MORE EXPOSED can be used to find and return the number of the required column. This will make the argument col_index_num (column_number) dynamic, i.e. it will be possible to insert new columns into the table without affecting the operation of the function VPR.
The formula shown below can be used in this example to solve the problem described above.
=VLOOKUP(I3,B3:G11,MATCH(J2,B2:G2,0),FALSE)
=ВПР(I3;B3:G11;ПОИСКПОЗ(J2;B2:G2;0);ЛОЖЬ)
The table got bigger
As new rows are added to the table, the functions VPR adjustment may be required to ensure that the new table rows are covered by the formula. The figure below shows the situation when the function VPR does not look through the entire table in search of the desired fruit.
Solution
Format a range of cells as a table (Excel 2007+) or as a named range. Such practices will ensure that VPR will always process the entire table.
To format a range as a table, select the range of cells you want to use for the argument table_array (table). On the Menu Ribbon, click Home > Format as Table (Home > Format as Table) and select a style from the gallery. Open a tab Table Tools > Design (Table Tools > Design) and change the table name in the corresponding field.
The formula in the figure below uses the table name FruitList.
VLOOKUP can’t look left
Function VPR has the following limitation: it cannot retrieve information from columns to the left of the first one. It looks up the value in the leftmost column of the given table and returns the information to the right.
Solution
The solution to this problem is not to use VPR at all. Use a combination of features INDEX (INDEX) and MATCH (SEARCH), which has become a familiar alternative for VPR. This is a much more flexible solution.
The example below was used to extract information from the column to the left of the one being searched:
=INDEX(B3:B13,MATCH(H3,C3:C13,0))
=ИНДЕКС(B3:B13;ПОИСКПОЗ(H3;C3:C13;0))
The data in the table is duplicated
Function VPR can extract only one entry. It will return the first record found that matches the search term you entered.
If the table contains duplicate values, the function VPR won’t do it right.
Solution 1
Do you need duplicate data in the list? If not, delete them. This can be done quickly with the button Removes Duplicates (Remove Duplicates) tab Data (Data).
Solution 2
Decided to leave duplicates? Good! In this case, you do not need a function VPR. Perfect for such cases. pivot table, which allows you to select a value and see the results.
The table below is a list of orders. Let’s say you want to find all orders for a particular fruit.
summary table allows you to select a value from a column ID in the filter that matches a particular fruit and get a list of all related orders. In our example, the value selected ID equal 23 (Bananas).
VFR without worries
This article shows solutions to the 6 most common causes of feature failure. VPR. Armed with this information, you can enjoy a more carefree future in the company of great Excel features.