Functions similar to ex. Named ranges and the intersection operator. Finding an exact match using VLOOKUP

We all know how useful it is. Probably half of all actions in Excel are performed using it. However this function has a number of limitations. For example, a VLOOKUP looks only at the leftmost column in a table or looks for only one condition. But what if we need to return a value that matches two conditions. In this case, we will have to resort to some tricks. We will talk about these tricks in today's article.

So, we will look at four options for creating a substitution function with two conditions:

Using the SUMPRODUCT function

Well, we’ll start with the simplest thing.

Using an additional column

In most cases complex problems become easier and more manageable when broken down into small pieces. The same applies when building formulas in Excel.

Let's look at a classic example. We have a table with sales by month and city. And we need to determine the sales value corresponding to two conditions: month - February and city - Samara.

Using the VLOOKUP function in classic look will not help us, since it can return a value that matches only one condition. An additional column will help us get out of this situation, in which we will combine the values of the Month and City columns. To do this, write the formula =B2&C2 in cell A2 and extend this formula to cell A13. Now we can use the values of column A to return the required value. We write the formula in cell G3:

VLOOKUP(G1A2:D13;4;0)

This formula combines the two conditions of cells G1 and G2 into one row and looks at it in column A. After necessary condition was found, the formula returns the value from the fourth column of table A1:D13, i.e. column Sales.

Using the SELECT function to create a new lookup table

If for some reason using an additional column is not an option for us, we can use .

Using the SELECT function involves creating new table to view where the column values Month And City already merged. Our formula will look like this:

VLOOKUP(G1SELECT((1,2),B2:B13&C2:C13,D2:D13),2,0)

The main point of this formula is the CHOICE((1;2);B2:B13&C2:C13;D2:D13) part, which does two things:

Combines column values Month And City in one array: JanMoscow, FebMoscow...
Combines two arrays into a two-column table.

The result of this function will be a table that looks like this:

Now the formula has become more clear.

IMPORTANT: Since we used an array formula, when you finish entering the formula, press Ctrl+Shift+Enter to let the program know our intentions. After pressing this key combination, the program will automatically install curly braces at the beginning and end of the formula.

Using the INDEX and MATCH functions

The third method, which we will consider, also involves the use of an array formula and uses the INDEX and SEARCH functions.

The formula will look like this.

INDEX(D2:D13,MATCH(1,(B2:B13=G1)*(C2:C13=G2),0))

Let's look at what each part of this formula does.

First, consider the MATCH(1;(B2:B13=G1)*(C2:C13=G2);0) function. IN in this case sequentially compares the value of cell G1 with each value of cells in the range B2:B13 and returns TRUE if the values match and FALSE if they do not. The same comparison is made with the value of cell G2 and the range C2:C13. Next we compare both of these arrays consisting of TRUE and FALSE. The combination TRUE * TRUE gives us the result 1 (TRUE). Let's look at the picture below, which will help explain the principle of operation more clearly.

Now we can tell where the string that satisfies both conditions is located. The MATCH function finds the position of 1 in the result array and returns 6 because the 1 occurs in the sixth row. Next, the INDEX function returns the value of the sixth row of the range D2:D13.

Using SUMPRODUCT

One of the most powerful Excel formulas. I even have a separate article dedicated to this formula. Our fourth way to use multiple conditions is to write a formula with the SUMPRODUCT function. And it will look like this:

SUMPRODUCT((B2:B13=G1)*(C2:C13=G2);D2:D13)

The principle of operation of this formula is similar to the principle of operation of the previous approach. Created virtual table, which compares the values of cells G1 and G2 with the ranges B2:B13 and C2:C13, respectively. Next, both of these arrays are compared and an array of ones and zeros is obtained, where one is assigned to the row in which both conditions match. Next, this virtual array is multiplied by the range D2:D13. Since our virtual array will only have one 1 in the sixth row, the formula will return the result 189.

This function will not work if there are text values in the range D2:D13.

To understand how it works this formula, I recommend reading the article about the SUMPROIZ function.

RESULT

So which method should you use? Although they all work reliably, I prefer the first method. In his daily work, I prefer to work with files that are easy to understand and changeable. Both of these requirements meet the conditions of the first approach.

For a better understanding of the formulas, you can look at them in today's article.

Any user who works with the LOOKUP function realizes after a while that it is not very convenient to work with a function that searches only to the right of the column in which the searched value is located. The MATCH and INDEX functions can solve this problem, but the overall formula will be much longer and more complex than a simpler VLOOKUP.

Custom Add-on rLOOKUP works like VLOOKUP, but allows you to perform a reverse search. The function simply uses the index and match methods in VBA. It is provided in Excel add-in for ease of use.

Download add-on

Save the file to your computer and install it in Excel by selecting File -> Options -> Add-ons. Click on the button Go..., in the window that opens, select Review. Select the saved file and click OK to complete the installation. After installation, the message "Reverse_Lookup" will appear in the list of available add-ons and the rLOOKUP function will become available in all books.

Function description

The arguments for the rLOOKUP function are the same as for the VLOOKUP function, see screenshots below. To perform a reverse lookup, simply enter a negative number into the "Col_Index_Num" argument (see examples below). The formula will Lookup towards the left.

One of the differences between these two functions is that if Time-lapse_view omitted in the VLOOKUP, there will definitely be no result. However, the opposite is true for rLOOKUP, which by default returns the exact value. Again, this is because more often than not, users want to get the value from the search result. The error values returned by the rLOOKUP function are similar to those returned by VLOOKUP.

Function example

Next Excel data shows office addresses, employee names and position.

To answer this query "Who works in office B43?" we can use the VLOOKUP function:

VLOOKUP("B43"; A1:C6;2; FALSE) returns "Dilbert".

However, if we want to know, "Where is Dilbert's office?" then VLOOKUP will not work. This is where the rLOOKUP function comes in handy:

rLOOKUP("Dilbert"; A1:B6, -2).

Please note that the column number is indicated in the arguments negative number-2, which allows you to search in the left column.

In general, the function works in both directions:

rLOOKUP("B43";A1:C6;2) returns "Dilbert" and

rLOOKUP("Dilbert"; A1:B6; -2) returns "B43".

Other examples are given below:

Remember to install the add-on first, otherwise an error will be returned #NAME?.

How the function works

Function uses functions MATCH and INDEX to run a reverse search.

See the source code for the function below.

Function rLOOKUP(Lookup_value, Table_array As Range, Col_index_num As Integer, Optional Range_lookup As Boolean)
"
"Custom function that allows forward and reverse vertical lookups
"Works like VLookup for positive Col_index_num, and does reverse lookups for negative Col_index_num
"Unlike VLOOKUP, it looks for the exact value by default
"
"Nitin Mehta, www.Engineers-Excel.com
"
"
Dim Source_Col As Range, Dest_Col_num As Integer

"The Source Column is the Column where the Lookup_value is located. It is the leftmost column if
"Col_index_number is positive. If Col_index_number is negative, it is the rightmost column
Set Source_Col = Table_array.Columns(IIf(Col_index_num > 0, 1, Table_array.Columns.Count))

"Dest_col_num is the column number within the Table_array range from where we have to pick up the entry against the Lookup_value
Dest_Col_num = IIf(Col_index_num > 0, Col_index_num, Table_array.Columns.Count + Col_index_num + 1)

"Use MATCH and INDEX functions for the Lookup.
"Use of CVErr(xlErrRef) suggested by Brian Canes
rLOOKUP = IIf(Dest_Col_num< 1, CVErr(xlErrRef), Application.Index(Table_array, Application.Match(Lookup_value, Source_Col, Range_lookup), Dest_Col_num))

End Function

Leave your comment!

Necessary Preface

If you have not worked with the function before VLOOKUP, then you have lost a lot, I highly recommend reading it first.

Problem

As many people know, the function VLOOKUP can return as a result values that are strictly to the right of the column where the search is performed. Oh, how beautiful it would be if the third argument of this function (the number of the column from which the values are returned) could be set to negative, but no.

In practice, situations often arise when you need to look for data in a column that is located to the right, and not to the left, of the results column, for example:

It’s easy to find the cost by order code - a regular VLOOKUP will help here once or twice. But how to find the name of the product by code? At trainings, I most often hear this question in the formulation “how to do a left VLOOKUP”?

Let's look at a few ways.

Method 1. Frontal attack

If you follow Occam’s principle and don’t complicate it unnecessarily, you can simply copy the desired column to the right (or make it links) and use the usual VLOOKUP:

Cheap and cheerful, but requires manual finishing of the table. In addition, there are often cases when a table cannot be changed: it is password protected, this corporate template, table in public access etc. Then a different approach is needed.

Method 2. Virtually rearranging columns using the SELECT function

If it is impossible to rearrange the columns on the sheet, then this can be done virtually, i.e. "on the fly" right in the formula itself. For this we need a function CHOOSE. Its main purpose is to select the desired element from the list by a given number. For example, it can be used to replace the day of the week number with its text counterpart:

Nothing supernatural at first glance, but there are a couple of tricky moments here.

Firstly, instead of the text names of the selected list elements ("Mon", "Tue", etc.), you can use range addresses. And then the function will return a link to the selected range. So, for example, the formula:

SELECT(2; A1:A10 ; D1:D10 ; B1:B10 )

Secondly, instead of a simple single number of the element to be retrieved in the first argument of the function CHOICE You can specify an array of constants in curly braces, for example, like this:

CHOICE( {1;2} ; A1:A10 ; D1:D10 ; B1:B10)

Then at the output we will get the first two ranges (A1:A10 and D1:D10), glued together into a single whole.

And now all this can be put inside our VLOOKUP to implement "left search":

It differs from the “classic VLOOKUP”, as you can see, only in that the range is specified by gluing two columns Order code And Product using the function CHOICE. Otherwise everything is as usual.

The disadvantages of this method are the speed (about 5-7 times slower than a regular VLOOKUP) and some unfamiliarity for colleagues (and maybe this is even a plus!)

Method 3. Linking the INDEX and SEARCH functions

If you don’t stick to the VLOOKUP function, you can use its more powerful analogue - a combination of two very useful functions INDEX And MATCH:

Function SEARCH looking for set value(C2, i.e. the code of the order we need) in a one-dimensional range (column of codes in the table C10:C25) and gives as a result the serial number of the cell where it found what we were looking for - in our case it will be the number 4, because The order code we need is fourth in the table.

And then the function comes into play INDEX, which can extract data from a vertical column array (product names in B10:B25) by serial number (which it previously found SEARCH). Thus, INDEX will give us the contents of the fourth cell from the column Product, which is what was required.

Compared with the previous way, this option is recalculated much faster (almost as fast as a regular VLOOKUP), which is important for large tables.

I analyzed a similar example (with video) earlier. And about the function INDEX you can speak :)

Method 4. SUMIF(MN) function

If you need to extract exactly a number from a table (for example, volume in liters), then sometimes it is easier to use the selective summation function to implement the “left VLOOKUP” SUMIF or its older sister - function SUMIFS:

The disadvantages of this approach are obvious - it only works for numbers and, provided that there are no duplicate values in the column. If there are duplicates (several orders with the same code), then this function will add all the volumes, and will not return the first one, as VLOOKUP would do. Well, the speed of this method is also not very good - about 3-4 times slower than a regular VLOOKUP.

Large article about selective counting functions based on one or more conditions.

Method 5: Ready-made macro function from PLEX

If you are not afraid of using macros, then you can use a ready-made custom function on Visual Basic, which is included in the Microsoft Excel. Compared to a regular VLOOKUP, it can:

search in several columns at once (up to 3)
produce results from any column (to the left or to the right - it doesn’t matter)
output not only the first value encountered, but the required one in order
you can specify what to display if nothing is found instead of the #N/A error

This method has two disadvantages: you need to save a file with macro support (XLSM) and the speed of any macro function is not very high - at large tables may noticeably slow down.

But as one of the options it will work :)

route	eid	prefix	payment
west2	251458	BR7652I	16.01.2017
Kashira1	252412	BR7652I	17.01.2017
S5-1	253016	BR7272I	18.01.2017
S3-1	254392	BR7652I	19.01.2017
Kashira1	255031	BR7249I	20.01.2017
Kashira1	257321	BR7759I	21.01.2017
SV1-1	257569	BR7761I	22.01.2017
Pushkino1	259373	BR7647I	23.01.2017
Kolomna1	259591	BR7315I	24.01.2017
Kashira1	260300	BR7544I	25.01.2017

prefix	eid
BR7652I	INDEX(A2:D11,MATCH(D20,C2:C11,0),MATCH(E19,A1:D1,0))

Beginning of work

To build the VLOOKUP function syntax you will need the following information:

The value you need to find is the value you are looking for.

The range in which the search value lies. Remember that for proper operation In the VLOOKUP function, the value you are looking for must always be in the first column of the range. For example, if the value you are looking for is in cell C2, the range must start at C.

The column number in the range that contains the return value. For example, if you specify B2:D11 as your range, you would consider B to be the first column, C to be the second, and so on.

Optionally, you can specify the word TRUE if you want an approximate match, or the word FALSE if you want an exact match of the return value. If you do not specify anything, the default is always TRUE, which is an approximate match.

Now combine all the above arguments as follows:

VLOOKUP(search value; range with search value; column number in range with return value; optionally TRUE for an approximate match or FALSE for an exact match).

Examples

Here are some examples of the VLOOKUP function:

Example 1

Example 2

Example 3

Example 4

Example 5

Common problems

Problem	Possible reason
Invalid return value	If the argument *interval_view* is TRUE or not specified, the first column must be sorted alphabetically or numerically. If the first column is not sorted, the returned value may be unexpected. Sort the first column or use FALSE for an exact match.
#N/A in cell	If the argument *interval_view* evaluates to TRUE and the value of the argument *lookup_value* less than the smallest value in the first column *tables* , the #N/A error value will be returned. If the argument *interval_view* is FALSE, the #N/A error value means that the exact number could not be found. additional information For troubleshooting #N/A errors in the VLOOKUP function, see the article Fixing the #N/A error in the VLOOKUP function.
	If the value " *Column_number* " is greater than the number of columns in *table* , you will receive #REF! Otherwise, TE102825393 will generate the error “#VALUE!”
#VALUE! in a cell	If *info_table* less than 1, you will get #VALUE! Otherwise, TE102825393 will generate the error “#VALUE!” More information about troubleshooting #VALUE! in the VLOOKUP function, see the article Fixing the #VALUE! in the VLOOKUP function.
#NAME? in a cell	Error value #NAME? most often appears when quotation marks are missing from a formula. When searching for an employee's name, make sure that the name in the formula is in quotation marks. For example, in the function =VLOOKUP("Ivanov";B2:E7;2;FALSE), the name must be specified in the format "Ivanov" and nothing else.

Action	Result
	Usage absolute links allows you to fill out a formula so that it always displays the same range of exact substitutions.
Do not save numeric or date values as text.	When searching numerical values or date values, make sure the data is in the first column of the argument *table* are not text values. Otherwise, the VLOOKUP function may return an incorrect or unexpected value.
Sort the first column	If for argument *interval_view* specified as TRUE, sort the first column before using the VLOOKUP function tables .
Use wildcards	If *Time-lapse_view* what matters is lies, and *Search_value* - text, wildcards can be used - question mark(_km_) and an asterisk () - in Search_value* . A question mark matches one character. An asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character. For example, using the function =VLOOKUP("Fontan?",B2:E7,2,FALSE) You can search for all uses of the surname Ivanov in various case forms.
Make sure the data does not contain erroneous characters.	When searching text values In the first column, ensure that the data does not contain leading or trailing spaces, illegal straight (" or ") or crooked (‘ or “) quotes, or non-printing characters. In these cases, the VLOOKUP function may return an unexpected value. To get accurate results, try using the functions

VLOOKUP is an Excel function that allows you to search a specific column using data in another column. The VLOOKUP function in Excel is also used to transfer data from one table to another. There are three conditions:

Tables must be located in one Excel workbook.
You can only search among static data (not formulas).
The search term must be in the first column of the data used.

VLOOKUP formula in Excel

VLOOKUP syntax in Russified Excel it looks like:

VLOOKUP (search criterion; data range; column number with result; search condition)

The arguments required to search for the final result are indicated in parentheses.

Search criteria

Cell address Excel sheet, which specifies the data to search in the table.

Data range

All addresses among which the search is carried out. The first column should be the one in which the search criterion is located.

Column number for total value

The column number from which the value will be taken if a match is found.

Search condition

A Boolean value (true/1 or false/0) that specifies whether to look for an approximate match (1) or an exact match (0).

VLOOKUP in Excel: function examples

The principle of operation of the function is simple. The first argument contains the search criteria. As soon as a match is found in the table (second argument), information is taken from the desired column (third argument) of the found row and inserted into the cell with the formula.
Simple application of VPR– search for values in an Excel table. It has meaning in large volumes of data.

Let's find the number of products actually released by the name of the month.
The result will be displayed to the right of the table. In the cell with address H3 we will enter the desired value. In the example, the name of the month will be indicated here.
In cell H4 we enter the function itself. This can be done manually, or you can use a wizard. To call, place the pointer on cell H4 and click the Fx icon next to the formula bar.

A wizard window will open Excel functions. You need to find a VLOOKUP in it. Select “Full Alphabetical List” from the drop-down list and start typing VLOOKUP. Highlight the found function and click OK.

A VLOOKUP window will appear for Excel tables.

To specify the first argument (criteria), place the cursor in the first row and click on cell H3. Her address will appear in the line. To select a range, place the cursor in the second line and start selecting with the mouse. The window will be minimized to a line. This is done so that the window does not interfere with your ability to see the entire range and does not interfere with your actions.

Once you have finished selecting and release left button mouse, the window will return to its normal state, and the address of the range will appear in the second line. It is calculated from the top left cell to the bottom right cell. Their addresses are separated by the operator “:” - all addresses between the first and last are taken.

Move the cursor to the third line and read from which column the data will be taken if a match is found. In our example it is 3.

Leave the last line blank. By default the value will be 1, let's see what value our function will output. Click OK.

The result is discouraging. "N/A" means invalid data for the function. We haven't specified a value in cell H3 and the function is looking for a blank value.

Enter the name of the month and the value will change.

Only it does not correspond to reality, because the actual actual number of products produced in January is 2000.
This is the effect of the Search Condition argument. Let's change it to 0. To do this, place the pointer on the cell with the formula and press Fx again. In the window that opens, enter “0” in last line.

Click "OK". As you can see, the result has changed.

To check the second condition from the beginning of our article (the function does not search among formulas), let’s change the conditions for the function. Let's increase the range and try to derive a value from a column with calculated values. Enter the values as in the screenshot.

Click OK. As you can see, the search result turned out to be 0, although the value in the table is 85%.

VLOOKUP in Excel “understands” only fixed values.

Comparing data from two Excel tables

VLOOKUP in Excel can be used to compare data from two tables. For example, let's say we have two sheets with data on product output from two workshops. We can match actual release for both. Let us remind you that to switch between sheets, use their shortcuts at the bottom of the window.

On two sheets we have identical tables with different data.

As you can see, their release plan is the same, but the actual one is different. Switch and compare line by line even for small volumes the data is very inconvenient. On the third sheet, create a table with three columns.

In cell B2 we enter the VLOOKUP function. As the first argument, we will indicate the cell with the month on the current sheet, and select the range from the “Workshop1” sheet. To prevent the range from shifting when copying, press F4 after selecting the range. This will make the link absolute.

Extend the formula to cover the entire column.

Similarly, enter the formula in the next column, only select the range on the “Workshop2” sheet.

After copying, you will receive a summary report from two sheets.

Substituting data from one Excel table to another

This action is performed in the same way. For our example, you don’t need to create separate table, but simply enter the function into a column of any of the tables. Let's show it using the first example. Place the pointer in the last column.

And in cell G3 place the VLOOKUP function. We again take the range from the adjacent sheet.

As a result, the column of the second table will be copied to the first.

That's all the information about the invisible, but useful function VLOOKUP in Excel for dummies. We hope it will help you in solving problems.

Have a great day!

Argument name	Description
lookup_value(required)	Search value. The value you are looking for must be in the first column of the cell range specified in *table* . For example, if *Array table* covers cells B2:D7, then the lookup_value should be in column B. See the picture below. *Search_value* can be a value or a cell reference.
table(required)	The range of cells in which the search will be performed *search_value* and the return value using the VLOOKUP function. The first column in the cell range must contain *lookup_value* (for example, last name, as shown in the figure below). The range of cells must also contain the return value (such as the name as shown in the image below) that you want to find.
column_number(required)	Column number (starting with 1 for leftmost column *tables* ) containing the return value.
interval_view(optional)	A Boolean value that specifies what match the function should find VLOOKUP, - approximate or exact. Option TRUE assumes that the first column in the table is sorted in alphabetical order or by numbers, and then searches for the closest value. This is the default method unless specified. Option LIE searches exact value in the first column.