Contents
- Basic information about INDEX and MATCH
- How to use INDEX and MATCH in Excel
- Why is INDEX/MATCH better than VLOOKUP?
- INDEX and MATCH – examples of formulas
This tutorial talks about the main benefits of the functions INDEX и MORE EXPOSED in Excel, which make them more attractive compared to VPR. You will see several examples of formulas that will help you easily deal with many of the complex tasks that the function faces. VPR powerless.
In several recent articles, we have made every effort to explain the basics of the function to novice users. VPR and show examples of more complex formulas for advanced users. Now we will try, if not to dissuade you from using VPR, then at least show alternative ways to implement vertical search in Excel.
Why do we need this? – you ask. Yes, because VPR is not the only search function in Excel, and its many limitations can prevent you from getting the result you want in many situations. On the other hand, the functions INDEX и MORE EXPOSED – more flexible and have a number of features that make them more attractive than VPR.
Basic information about INDEX and MATCH
Since the purpose of this tutorial is to show the capabilities of the functions INDEX и MORE EXPOSED to implement vertical search in Excel, we will not dwell on their syntax and application.
We give here the necessary minimum for understanding the essence, and then we will analyze in detail examples of formulas that show the advantages of using INDEX и MORE EXPOSED instead VPR.
INDEX – syntax and application of the function
Function INDEX (INDEX) in Excel returns a value from an array at the given row and column numbers. The function has the following syntax:
INDEX(array,row_num,[column_num])
ИНДЕКС(массив;номер_строки;[номер_столбца])
Each argument has a very simple explanation:
- array (array) is the range of cells from which to extract the value.
- row_num (line_number) is the row number in the array from which to extract the value. If not specified, an argument is required column_num (column_number).
- column_num (column_number) is the number of the column in the array from which to extract the value. If not specified, an argument is required row_num (line_number)
If both arguments are given, then the function INDEX returns the value from the cell at the intersection of the specified row and column.
Here is a simple function example INDEX (INDEX):
=INDEX(A1:C10,2,3)
=ИНДЕКС(A1:C10;2;3)
The formula searches in a range A1: C10 and returns the value of the cell in 2-th line and 3-m column, that is, from a cell C2.
Very simple, right? However, in practice, you do not always know which row and column you need, and therefore you need the help of the function MORE EXPOSED.
MATCH – syntax and application of the function
Function MATCH (MATCH) in Excel searches for a specified value in a range of cells and returns the relative position of that value in the range.
For example, if in the range B1: B3 contains the values New-York, Paris, London, then the following formula will return a digit 3, because “London” is the third item in the list.
=MATCH("London",B1:B3,0)
=ПОИСКПОЗ("London";B1:B3;0)
Function MATCH (MATCH) has the following syntax:
MATCH(lookup_value,lookup_array,[match_type])
ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])
- lookup_value (lookup_value) is the number or text you are looking for. The argument can be a value, including a boolean, or a cell reference.
- lookup_array (lookup_array) is the range of cells in which to search.
- match_type (match_type) – This argument tells the function MORE EXPOSEDwhether you want to find an exact match or an approximate match:
- 1 or not specified – finds the maximum value less than or equal to the desired one. The array being looked up must be sorted in ascending order, that is, from smallest to largest.
- 0 – finds the first value equal to the desired one. For combination INDEX/MORE EXPOSED you always want an exact match, so the third function argument MORE EXPOSED should be equal 0.
- -1 – finds the smallest value greater than or equal to the desired value. The array being looked up must be sorted in descending order, i.e. from largest to smallest.
At first glance, the benefits of the function MORE EXPOSED raises doubts. Who needs to know the position of an element in a range? We want to know the value of this element!
Let me remind you that the relative position of the value you are looking for (i.e. the row and/or column number) is exactly what we need to specify for the arguments row_num (line_number) and/or column_num (column_number) functions INDEX (INDEX). As you remember, the function INDEX can return the value at the intersection of the given row and column, but it cannot determine which row and column we are interested in.
How to use INDEX and MATCH in Excel
Now that you know the basic information about these two functions, I believe that it is already becoming clear how the functions MORE EXPOSED и INDEX can work together. MORE EXPOSED defines the relative position of the searched value in the given range of cells, and INDEX uses that number (or numbers) and returns the result from the corresponding cell.
Still not entirely clear? Represent Features INDEX и MORE EXPOSED in this form:
=INDEX(столбец из которого извлекаем,(MATCH (искомое значение,столбец в котором ищем,0))
=ИНДЕКС(столбец из которого извлекаем;(ПОИСКПОЗ(искомое значение;столбец в котором ищем;0))
I think it will be easier to understand with an example. Suppose you have the following list of state capitals:
Let’s find the population of one of the capitals, say Japan, using the following formula:
=INDEX($D$2:$D$10,MATCH("Japan",$B$2:$B$10,0))
=ИНДЕКС($D$2:$D$10;ПОИСКПОЗ("Japan";$B$2:$B$10;0))
Now let’s break down what each element of this formula does:
- Function MATCH (MATCH) looks for the value “Japan” in a column B, and specifically in the cells B2: B10, and returns a number 3, since “Japan” is in third place in the list.
- Function INDEX (INDEX) uses 3 for argument row_num (line_number), which specifies from which row to return the value. Those. a simple formula is obtained:
=INDEX($D$2:$D$10,3)=ИНДЕКС($D$2:$D$10;3)The formula says something like this: look in cells from D2 to D10 and extract the value from the third row i.e. from the cell D4, since the counting starts from the second line.
Here is the result in Excel:
Important! The number of rows and columns in the array that the function uses INDEX (INDEX), must match the argument values row_num (line_number) and column_num (column_number) functions MATCH (SEARCH). Otherwise, the result of the formula will be erroneous.
Stop, stop… why can’t we just use the function VLOOKUP (VLOOKUP)? Is there any point in wasting time trying to figure out the labyrinths MORE EXPOSED и INDEX?
=VLOOKUP("Japan",$B$2:$D$2,3)
=ВПР("Japan";$B$2:$D$2;3)
In this case, it makes no sense! The purpose of this example is purely illustrative, so that you can understand how the functions MORE EXPOSED и INDEX work in pairs. The following examples will show you the true power of the bundle. INDEX и MORE EXPOSED, which easily copes with many difficult situations when VPR gets stuck.
Why is INDEX/MATCH better than VLOOKUP?
When deciding which formula to use for vertical search, most Excel gurus believe that INDEX/MORE EXPOSED much better than VPR. However, many Excel users still resort to using VPR, because this function is much easier. This happens because very few people fully understand the full benefits of switching from VPR on a bunch INDEX и MORE EXPOSED, and no one wants to spend time studying a more complex formula.
Next, I will try to outline the main advantages of using MORE EXPOSED и INDEX in Excel, and you decide to stay with VPR or switch to INDEX/MORE EXPOSED.
Top 4 benefits of using MATCH/INDEX in Excel:
1. Search from right to left. As any literate Excel user knows, VPR cannot look to the left, which means that the desired value must necessarily be in the leftmost column of the range under study. In case of MORE EXPOSED/INDEX, the search column can be either on the left or on the right of the search range. Example: How to find values that are on the left will show this feature in action.
2. Safe addition or removal of columns. Formulas with a function VPR stop working or return erroneous values if you remove or add a column to the lookup table. For function VPR any column inserted or removed will change the result of the formula because the syntax VPR requires you to specify the entire range and the specific column number from which you want to extract data.
For example, if you have a table A1: C10, and you want to extract data from a column B, then you need to set the value 2 for argument col_index_num (column_number) functions VPR, like this:
=VLOOKUP("lookup value",A1:C10,2)
=ВПР("lookup value";A1:C10;2)
If you later insert a new column between columns A и B, then the value of the argument will have to be changed from 2 on 3, otherwise the formula will return the result from the column just inserted.
Using MORE EXPOSED/INDEX, You can remove or add columns to the examined range without distorting the result, since the column containing the desired value is directly defined. Indeed, this is a great advantage, especially when you have to work with large amounts of data. You can add and remove columns without worrying about having to fix every feature you use VPR.
3. There is no limit on the size of the searched value. Using VPR, be aware of the 255-character limit on the length of the searched value, otherwise you risk getting an error #VALUE! (#VALUE!). So, if the table contains long rows, the only viable solution is to use INDEX/MORE EXPOSED.
Let’s say you use the following formula: VPR, which searches in cells from B5 to D10 the value specified in the cell A2:
=VLOOKUP(A2,B5:D10,3,FALSE)
=ВПР(A2;B5:D10;3;ЛОЖЬ)
The formula will not work if the value is in the cell A2 longer than 255 characters. Instead, you need to use a similar formula INDEX/MORE EXPOSED:
=INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))
=ИНДЕКС(D5:D10;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B5:B10=A2;0);0))
4. Higher running speed. If you are working with small spreadsheets, then the difference in Excel performance will most likely not be noticeable, especially in recent versions. If you are working with large tables that contain thousands of rows and hundreds of search formulas, Excel will run much faster when you use MORE EXPOSED и INDEX instead VPR. In general, such a replacement increases the speed of Excel by 13%.
Impact VPR Excel performance is especially noticeable if the workbook contains hundreds of complex array formulas, such as VLOOKUP+SUM. The fact is that checking each value in the array requires a separate function call VPR. Therefore, the more values an array contains and the more array formulas your table contains, the slower Excel is.
On the other hand, a formula with functions MORE EXPOSED и INDEX simply performs a lookup and returns the result, doing the same job noticeably faster.
INDEX and MATCH – examples of formulas
Now that you understand the reasons why you should learn functions MORE EXPOSED и INDEX, let’s move on to the most interesting and see how you can apply theoretical knowledge in practice.
How to search on the left side using MATCH and INDEX
Any textbook VPR says that this function cannot look to the left. Those. if the column being looked up is not the leftmost column in the search range, then there is no chance of getting from VPR desired result.
functions MORE EXPOSED и INDEX in Excel they are much more flexible, and they don’t care where the column with the value to be extracted is located. For example, let’s go back to the table with state capitals and population. This time we write the formula MORE EXPOSED/INDEX, which will show what place the capital of Our Country (Moscow) occupies in terms of population.
As you can see in the figure below, the formula does a great job of this task:
=INDEX($A$2:$A$10,MATCH("Our Country",$B$2:$B$10,0))
=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ("Our Country";$B$2:$B$10;0))
Now you should have no problem understanding how this formula works:
- First, let’s use the function MATCH (MATCH) that finds the position “Our Country” in the list:
=MATCH("Our Country",$B$2:$B$10,0))=ПОИСКПОЗ("Our Country";$B$2:$B$10;0)) - Next, set the range for the function INDEX (INDEX) to retrieve the value from. In our case, this A2: A10.
- Then we connect both parts and get the formula:
=INDEX($A$2:$A$10;MATCH("Our Country";$B$2:$B$10;0))=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ("Our Country";$B$2:$B$10;0))
Tip: The correct solution is to always use absolute references for INDEX и MORE EXPOSEDso that the search ranges do not get lost when copying the formula to other cells.
Calculations using INDEX and MATCH in Excel (AVERAGE, MAX, MIN)
You can nest other Excel functions in INDEX и MORE EXPOSED, for example, to find the minimum, maximum, or closest to the average value. Here are some options for formulas, in relation to the table from the previous example:
1. MAX (MAX). The formula finds the maximum in a column D and returns the value from the column C the same line:
=INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МАКС($D$2:I$10);$D$2:D$10;0))
Result: Beijing
2. MIN (MIN). Formula finds the minimum in a column D and returns the value from the column C the same line:
=INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МИН($D$2:I$10);$D$2:D$10;0))
Result: Lima
3. AVERAGE (AVERAGE). The formula calculates the average in the range D2: D10, then finds the one closest to it and returns the value from the column C the same line:
=INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(СРЗНАЧ($D$2:D$10);$D$2:D$10;1))
Result: Moscow
Points to remember when using the AVERAGE function with INDEX and MATCH
Using the function AVERAGE in combination with INDEX и MORE EXPOSED, as the third function argument MORE EXPOSED most often you will need to specify 1 or -1 in case you are not sure that the viewed range contains a value equal to the average. If you are sure that there is such a value, put 0 to find an exact match.
- If you specify 1, the values in the lookup column should be sorted in ascending order, and the formula will return the maximum value less than or equal to the mean.
- If you specify -1, the values in the lookup column should be sorted in descending order, and the minimum value greater than or equal to the average will be returned.
In our example, the values in the column D are sorted in ascending order, so we use the match type 1. Formula INDEX/MOREЗ returns “Moscow” because the population of Moscow is the nearest lower value to the mean (12).
How to use INDEX and MATCH to search on a known row and column
This formula is equivalent to a XNUMXD search VPR and allows you to find the value at the intersection of a certain row and column.
In this example, the formula INDEX/MORE EXPOSED will be very similar to the formulas that we have already discussed in this lesson, with only one difference. Guess what?
As you remember, the function syntax INDEX (INDEX) allows three arguments:
INDEX(array,row_num,[column_num])
ИНДЕКС(массив;номер_строки;[номер_столбца])
And congratulations to those of you who figured it out!
Let’s start by writing the formula template. To do this, we take the already familiar formula INDEX/MORE EXPOSED and add another function to it MORE EXPOSED, which will return the column number.
=INDEX(Ваша таблица,(MATCH(значение для вертикального поиска,столбец, в котором искать,0)),(MATCH(значение для горизонтального поиска,строка в которой искать,0))
=ИНДЕКС(Ваша таблица,(MATCH(значение для вертикального поиска,столбец, в котором искать,0)),(MATCH(значение для горизонтального поиска,строка в которой искать,0))
Please note that for a two-dimensional search, you need to specify the entire table in the argument array (array) functions INDEX (INDEX).
Now let’s put this pattern to the test. Below you see a list of the most populated countries in the world. Suppose our task is to find out the population of the United States in 2015.
Okay, let’s write the formula. When I need to create a complex formula in Excel with nested functions, I first write each nested one separately.
So let’s start with two functions MORE EXPOSED, which will return the row and column numbers for the function INDEX:
- MATCH for column – we are looking in a column B, to be more precise, in the range B2: B11, the value that is specified in the cell H2 (USA). The function will look like this:
=MATCH($H$2,$B$1:$B$11,0)=ПОИСКПОЗ($H$2;$B$1:$B$11;0)The result of this formula will be 4, since “USA” is the 4th element of the list in the column B (including title).
- MATCH for string – we are looking for the value of the cell H3 (2015) in line 1, that is, in the cells A1: E1:
=MATCH($H$3,$A$1:$E$1,0)=ПОИСКПОЗ($H$3;$A$1:$E$1;0)The result of this formula will be 5, because “2015” is in the 5th column.
Now we insert these formulas into the function INDEX and voila:
=INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))
=ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($H$2;$B$1:$B$11;0);ПОИСКПОЗ($H$3;$A$1:$E$1;0))
If we replace the functions MORE EXPOSED on the values they return, the formula will become easy and understandable:
=INDEX($A$1:$E$11,4,5))
=ИНДЕКС($A$1:$E$11;4;5))
This formula returns the value at the intersection 4-oh lines and 5-th columns in range A1: E11, that is, the value of the cell E4. Just? Yes!
Search by multiple criteria with INDEX and MATCH
In the textbook on VPR we showed an example of a formula with a function VPR to search by multiple criteria. However, a significant limitation of this solution was the need to add an auxiliary column. The Good News: The Formula INDEX/MORE EXPOSED can search by values in two columns, without the need to create an auxiliary column!
Suppose we have a list of orders and we want to find the sum by two criteria − buyer’s name (Customer) и product (Product). The matter is complicated by the fact that one buyer can buy several different products at once, and the names of buyers in the table on the sheet Lookup table arranged in random order.
Here is the formula INDEX/MORE EXPOSED solves the problem:
{=INDEX('Lookup table'!$A$2:$C$13,MATCH(1,(A2='Lookup table'!$A$2:$A$13)*
(B2='Lookup table'!$B$2:$B$13),0),3)}
{=ИНДЕКС('Lookup table'!$A$2:$C$13;ПОИСКПОЗ(1;(A2='Lookup table'!$A$2:$A$13)*
(B2='Lookup table'!$B$2:$B$13);0);3)}
This formula is more complicated than the others we discussed earlier, but armed with knowledge of the functions INDEX и MORE EXPOSED You will overcome her. The hardest part is the function MORE EXPOSEDI think it needs to be explained first.
MATCH(1,(A2='Lookup table'!$A$2:$A$13),0)*(B2='Lookup table'!$B$2:$B$13)
ПОИСКПОЗ(1;(A2='Lookup table'!$A$2:$A$13);0)*(B2='Lookup table'!$B$2:$B$13)
In the formula shown above, the value you are looking for is 1, and the lookup array is the result of the multiplication. Okay, so what should we multiply and why? Let’s take it all in order:
- Get the first value in the column A (Customer) to lists Main table and compare it with all the names of buyers in the table on the sheet Lookup table (A2:A13).
- If a match is found, the equation returns 1 (TRUE), and if not – 0 (FALSE).
- Next, we do the same for the column values B (Product).
- Then we multiply the obtained results (1 and 0). Only if matches are found in both columns (i.e. both criteria are true) you will get 1. If both criteria are false, or only one of them is met, you will get 0.
Now you understand why we asked 1as the desired value? It’s right that the function MORE EXPOSED returned the position only when both criteria are met.
Note: In this case, you must use the third optional function argument INDEX. It is necessary, because in the first argument, we specify the entire table and must tell the function which column to extract the value from. In our case, this is the column C (Sum) and so we introduced 3.
And finally, because we need to check each cell in the array, this formula must be an array formula. You can see this by the curly braces it is enclosed in. So when you’re done entering the formula, don’t forget to press Ctrl + Shift + Enter.
If everything is done correctly, you will get the result as in the figure below:
INDEX and MATCH combined with IFERROR in Excel
As you probably already noticed (and more than once), if you enter an incorrect value, for example, which is not in the array you are viewing, the formula INDEX/MORE EXPOSED reports an error #AT (#N/A) or #VALUE! (#VALUE!). If you want to replace such a message with something more understandable, you can insert a formula with INDEX и MORE EXPOSED into a function IFERROR.
Function syntax IFERROR very simple:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Where is the argument value (value) is the value being checked for an error (in our case, the result of the formula INDEX/MORE EXPOSED); and the argument value_if_error (value_if_error) is the value to return if the formula throws an error.
For example, you can paste the formula from the previous example into a function IFERROR like this:
=IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)),
"Совпадений не найдено. Попробуйте еще раз!") =ЕСЛИОШИБКА(ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($G$2;$B$1:$B$11;0);ПОИСКПОЗ($G$3;$A$1:$E$1;0));
"Совпадений не найдено. Попробуйте еще раз!")
And now, if someone enters an erroneous value, the formula will give the following result:
If you prefer to leave the cell empty in case of an error, you can use quotation marks (“”) as the value of the second function argument IFERROR. Like this:
IFERROR(INDEX(массив,MATCH(искомое_значение,просматриваемый_массив,0),"")
ЕСЛИОШИБКА(ИНДЕКС(массив;ПОИСКПОЗ(искомое_значение;просматриваемый_массив;0);"")
I hope that at least one of the formulas described in this tutorial seemed useful to you. If you have encountered other search problems for which you could not find a suitable solution among the information in this lesson, feel free to describe your problem in the comments, and we will all try to solve it together.