Professional VLOOKUP Formula Examples in Excel

It is much easier to understand how to use a formula (especially one as complex as VLOOKUP) with real examples. They will tell you how to apply it like a real professional does. The calculation is made on the fact that the user already has basic ideas about how to implement its functionality. And he needs to focus on more complex cases like multi-criteria search.

This is the second part of the instruction describing the functionality of the function. VPR(). Before considering special cases of its use, remember its syntax:

ВПР(lookup_value, table_array, col_index_num, [range_lookup])

And then we will look at how you can make its application more flexible.

How to use multiple criteria in a formula

If the user needs to find a specific value in a huge amount of information, the function VPR serve him very well. But unfortunately, its standard syntax allows you to search for only one value. But what if you need to apply several conditions at once? Here are some solutions to this difficult task.

Example 1: using two search terms

Let’s imagine such a situation. You have a list of orders open in front of you, and we need to understand how much of a particular product (Product) needs to be delivered to a particular person. 

That is, we have two criteria:

1. Buyer’s name.
2. Product name.

For clarity, here is a table.

Standard Formula Syntax VPR() does not provide a way to implement this task because it returns the first value it finds. But there is a way around this difficulty. To do this, you need to insert an auxiliary column in which all the required conditions are reduced to one. Do not forget that the auxiliary column is on the very left side, since this is where the search is carried out by this function.

Thus, we need to insert a new column on the right side and copy the formula =B2&C2 throughout the column. To increase the readability of a string, you can use a space to separate concatenated values =B2&” “&C2.

The following formula is used VPR for these values, as shown in the example:

=ВПР(“Jeremy Sweets”, A2:D11, 4, FALSE)

Or you can shorten this formula to:

=VLOOKUP(G1&” “&G2, A2:D11, 4, FALSE)

The number 4 in the formula means the number of the column from which the data is extracted.

For the formula to work correctly, the values ​​in the helper column must be combined in exactly the same way as in the Lookup Value argument. In the example we described, a space was used to separate the criteria both in the auxiliary column and in the formula VPR().

Example 2: Using multiple formula conditions

Theoretically, it is possible to adapt the example described above for more criteria. But there are a few things to consider here:

1. First, the maximum length of a string containing the search value is only 255 characters.
2. Secondly, the design of the table can be built in such a way that it will not be possible to add an auxiliary column.

Fortunately, Excel provides several solutions to this problem. One of them is using a combination of formulas INDEX() и EXPRESS(). Such a formula will be quite long, but you can do without adding an additional column.

For example, the formula might be:

=INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))

Microsoft Office 365 has recently added a feature LOOKUP().

An example of using the formula is as follows:

=XLOOKUP(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), D2:D11)

Deciphering both formulas in the case we are describing:

1. G1 is the first condition (date).
2. G2 – condition number 2 (client name).
3. G3 – search criterion 3 – (product name).
4. A2:A11 – the desired range 1.
5. B2:B11 is the search range containing the names of the clients.
6. C2:C11 – the desired range 3, which lists the names of the goods.
7. D2:D11 – range of returned values ​​(product quantity).

Using the VLOOKUP function to get values ​​beyond the first

As far as advanced Excel users know, the formula VPR() returns only the first matched value. But what if there are more matches and you want to get the second or third element? This task is difficult at first glance, but it is much easier to solve than it might seem at first.

Formula 1: Match n by VLOOKUP

Let’s say you have a table that lists the names of customers in one column and the items they purchased in another. And, for example, the task is to find the second or third product they bought.

The easiest way to do this is to add an auxiliary column to the left side of the table, as described from the very beginning. Each cell contains customer names with a number, for example, John Doe1, John Doe2. To do this, apply the formula COUNTIF, given that customers are in column B: 

=B2&COUNTIF($B$2:B2,B2)

This formula is sent to cell A2, and after that it can be copied to as many cells as needed.

Then you need to enter the target name and search number in separate cells (F1 and F2) and use this formula:

=VLOOKUP(F1&F2, A2:C11, 3, FALSE)

Formula 2: Finding the second occurrence

If you need to find the second occurrence, but for some reason it is not possible to add an auxiliary column, this can be done using the function INDIRECT() with EXPRESS():

=VLOOKUP(E1, INDIRECT(“A”&(MATCH(E1, A2:A11, 0)+2)&”:B11″, 2, FALSE)

In this formula:

1. E1 is the desired value.
2. A2:A11 is the range in which the search is performed.
3. B11 is the last cell in the table where the search is performed.

It is much easier to see how this function works with a real example.

Keep in mind that the formula above is for the special case where the lookup table starts on the second line. In the appropriate places, you need to substitute your own cell addresses and the desired range.

How this formula works

There is a key part to this formula that creates a dynamic search range. It is a combination of functions INDIRECT() и EXPRESS():

ДВССЫЛ(«A»&(ПОИСКПОЗ(E1, A2:A11, 0)+2)&»:B11″)

This function is designed so that each match is compared to the target name among the names. As a result, this formula returns the position of the first value found. In our case, this is the number 3, which is used as the starting coordinate for the search range. Therefore, you can add 2 to it (one to exclude the first occurrence, and a second one to exclude the first row with column headings).

The calculation of the required increase based on the position of the header line (in our case, this is line A1) can also be calculated using the formula. To do this, you need to use the formula 1+ROW(A1). Naturally, in place of A1 it is necessary to insert the cell that is most suitable in a particular situation.

As a result, we get the following string, which is converted to a range by the function INDIRECT().

This range is inserted in place of the array-to-look-up argument, and the formula starts looking for the required value on line 5, while the first value is skipped.

How to return multiple values ​​using the VLOOKUP function

Excel function VPR() does not provide for the ability to retrieve more than one value. But is there a way to get around this limitation? Of course yes. True, this method cannot be called simple. It requires the use of several functions at once, such as INDEX(), LEAST(), LINE().

For example, the following formula is able to find all the values ​​contained in cell F2 among cells that are within the range B2:B16. The result of calculations by this formula in the form of several values ​​is returned from the same rows in column C:

{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;»»);СТРОКА()-3));»»)}

This formula can be entered in two ways:

1. Enter it directly in the first cell. After that, you need to press the key combination Ctrl + Shift + Enter, and then copy it down to the required number of cells.
2. Select a range of values ​​within one column, as in the screenshot below, enter the formula, and then press the above key combination.

It is important that the number of cells in which the formula is entered is greater than or equal to the maximum number of possible matches.

Search by known column and row

If you know the column and row number, you can extract the value at certain coordinates. There are several different ways to do this kind of search in Excel, but as we are discussing the formula today VPR(), it will be used.

In this example, we will take a table with monthly discounts and use this formula to get the discount rate for a desired product at a certain time.

In our formula, product names are in the range A2:A9, months are B1:F1, and the target month is in cell I2.

In this case, the formula will take the following form:

=VLOOKUP(I1, A2:F9, MATCH(I2, A1:F1, 0), FALSE)

How this formula works

Within this formula, there is a standard function VPR(), which looks for an exact match of the desired value, which in our case is in cell I1. But since we don’t know exactly which column the required value is in, we can’t write the column number directly. For this, the function is used EXPOSE().

EXPRESS(I2, A1:F1, 0)

In simple terms, this formula says: look for the value in cell I2 in the range of values ​​A1:F1, then write the position of the found value in the array. By passing the value 0 to the third argument, the user tells the program to look for the same value as it is written in the search. This works in the same way as using the FALSE parameter in the formula VPR().

Since the required month in our case is on the fourth column of the desired array, the function EXPRESS() returns 4, which is written immediately in the argument col_index_num Features VPR().

VLOOKUP(I1, A2:F9, 4, FALSE)

In this case, you need to pay attention to the fact that although the names of the months are described in column B, you still need to search through the entire table. This is necessary to fully match the value returned by the function EXPRESS(), column positions in the argument table_array Features VPR().

Is it possible to use multiple VLOOKUPs in one formula

Sometimes it happens that the main table and the table being searched do not have a common column. This prevents the standard VPR function from being used. Despite this, there is another table that does not contain the information you are looking for, but it has a common column with the main table and the lookup table.

It is much easier to depict this with a screenshot.

The task is to copy the prices to the main table based on the ID. The difficulty lies in the fact that the table contains prices for goods that do not have their own ID. Therefore, it is necessary to use several formulas VPR() together.

To demonstrate this, you can create a couple of ranges.

1. The desired range 1 will be called “Products”.
2. Let the second desired range be called “Prices”.

They have addresses D3:E3 and G3:H3 respectively.

The tables may be the same or different.

After that, you need to register an action called double VPR or nested VPR.

First you need to create a formula VPR() in the first lookup table, which lists products based on the ID located in cell A3:

=VLOOKUP(A3, Products, 2, FALSE)

After that, you need to place the above formula in the lookup_value argument (lookup value) to get the prices from lookup table 2 (which lists the cost), based on the name of the product, which is returned nested VPR().

As a result, the following simple formula will turn out:

=VLOOKUP(VLOOKUP(A3, Products, 2, FALSE), Prices, 2, FALSE)

And here is a screenshot to make it more clear.

Dynamic substitution of data from different tables

To begin with, it is necessary to explain in more detail the meaning of the term “Dynamic data substitution”. Sometimes it may happen that there are two sheets with the same data format. And the person is faced with the task of extracting the required data based on what value is indicated in a particular cell. It is much easier to explain this with an example.

Let’s say you have several regional sales reports in the same format and you need to understand how sales are doing in specific regions.

Just like in the previous example, we first need to define the names of several ranges A2:B5 in different regions.

1. CA – CA_Sales.
2. FL – FL_Sales.
3. KS – KS_Sales.

As you can see, all named ranges have a common part – Sales, and also have unique parts denoting the region. Before proceeding further, you need to make sure that the ranges with different names have the same shape, otherwise the formula will not work.

Formula 1: Using a combination of INDIRECT and VLOOKUP formulas

If you need to get data from several tables at once, the best solution would be to use formulas at the same time INDIRECT() и VPR(). This is the easiest solution to understand and, among other things, is incredibly compact and can save a lot of time.

For example, we organize the general table in this way:

1. Enter the required products in cells A2 and A3. They will be our desired values.
2. Enter the unique parts of the names sequentially in cells B1, C1, D1.

Next, you need to combine the unique part with the common part and use the resulting value as a function parameter INDIRECT().

INDIRECT(B$1&”_Sales”)

This formula turns the string value into a readable form for Excel, after which the result of this function must be placed in the table_array argument:

=VLOOKUP($A2, INDIRECT(B$1&”_Sales”), 2, FALSE)

The above formula is printed in cell B2, after which its result can be copied down as many cells as necessary.

In this case, you need to pay attention that the desired value is fixed using an absolute reference. This makes it possible not to change the address of the cell that is used for the search, regardless of which cell the formula is copied to.

If the main table is organized in a different way, you need to fix the address with the $ sign:

=VLOOKUP(B$1, INDIRECT($A2&»_Sales»), 2, FALSE)

Formula 2: VLOOKUP and Nested IF

In a situation where there are only two or three search cells, you need to use a fairly simple variation of the formula VPR() with nested function IF()to select the correct cell based on the key value in a particular cell.

An example formula is as follows:

=VLOOKUP($A2, IF(B$1=”CA”, CA_Sales, IF(B$1=”FL”, FL_Sales, IF(B$1=”KS”, KS_Sales,””))), 2, FALSE)

In this formula, $A2 is the value to be found (element name) and B$1 is the value that specifies the region.

In this case, it is not strictly necessary to define titles, and external links can be used to refer to another cell or workbook.

There are many other examples of the formula VPR(). With due professionalism, the user can easily create his own combinations of several functions in order to achieve the necessary goals.

In extreme cases, you can always use macros, which make it possible to integrate into Excel those functions that are necessary to perform a specific task.