vlookup.co
Learn VLOOKUP HLOOKUP and INDEX MATCH In-depth

INDEX MATCH

Index and Match are separate formulas, and we are clubbing these two formulas to act like Vlookup. If you know Vlookup, then it is easy to learn Index Match. First-time users find it very complicated to learn. Obviously, it is complicated in a way, but you have to master it. It has a definite advantage over Vlookup like it can lookup left and will return correct result even if the in-between columns are deleted. We will look into each formula separately and then club these two together. Syntax: INDEX(array, row_num, [column_num]) This is the syntax for Index function and the third parameter column number is optional. We are not using the third parameter column number for Index Match function. What it does is it will fetch the data from the cell you specify within an array. For example, we have the words Jan in A1, Feb in A2, Mar in A3 and Apr in A4. Now if we write Index(A1:A4,2), it will fetch Feb from the array A1:A4 as it is the second cell in the array. So whatever number you give as second parameter it will fetch the corresponding value from the array. If it is one, it will fetch the first value and if it is two, it will fetch the second value. Now here in this example, we have manually entered the row number. But to behave like Vlookup, we have to fetch row number dynamically. For that, you have to use Match function. Syntax: MATCH ( lookup value , lookup array, match type ) Match functions syntax is somewhat similar to Vlookup function without the third parameter. The first parameter is the value we want to look. The second parameter is where to look; it will be a group of cells. The third parameter is for the match type, and you can use 1 for less than or O for exact match and -1 for greater than matches. Looking into the same example, we have used in the Index function if we write a Match function it will look like this, MATCH("Feb",A1:A4,False). Because Feb is the second position in the array from A1:A4 it will return the position number 2. If you just observe the syntax of Index and Match, Match is the reverse of Index function. In Index, you have to specify the array as the first parameter, and in Match you have to specify the array as the second parameter. Index returns the actual value in the cell, but Match will return the position of the data in the array, i.e., is 1 or 2 or 3. Example: Open the file Chapter 7.1 - IndexMatch.xlsx Now we will club these two formulas in this example. First, we will use both Index and Match separately and then we will club it. The outcome of this exercise is to find out the second value of the month from the array A2:A4. [example] Here you can see both Index and Match in action separately. First Index formula is checking the second value in the array A2:A5 and it fetches Feb. Enter the formula INDEX(A2:A5,2) in D2 cell. We are not using the third parameter of the Index function to get column number. Match function lookup for the value Feb and it returns the position number from A2:A5 which is 2. Now enter the formula =MATCH("Feb",A2:A5,0) in D3 cell. Now we will club these two functions. In the above Index formula, we have given the second parameter manually, but we can get it from Match function. So we will change the second parameter of Index function and included Match function to get the position number of the Feb in the array A2:45. This will return 2 and is supplied to the Index formula to get the answer Feb. Enter the formula =INDEX(A2:A5,MATCH("Feb",A2:A5,0)) in D5 cell and you will get the answer Feb. This is just a small example of Index Match. We will go through another example for you understand the power of Index Match. Example: Open the file Chapter 7.2 - IndexMatch.xlsx Here we have two sheets, sheet1 has six students name and their score in various subjects and sheet2 has five students name from sheet1. We want to compare the two sheets and if the student names match, we should fetch the Physics score from sheet1 to sheet2. Sheet1 image given below [example] Sheet2 image given below [example] Enter this formula in cell B2 of Sheet2. =INDEX(Sheet1!$D$2:$D$7,MATCH(Sheet2!A2,Sheet1!$A$2:$A$7,0)). Now we will break this formula and explain. INDEX(Sheet1!$D$2:$D$7, -The first parameter of Index function is the column from which we want the data if the name matches. If there is a match, we want the Physics score in the D2:D7 cells from Sheet1. We have given the dollar sign to lock the cells. MATCH(Sheet2!A2,Sheet1!$A$2:$A$7,0) - This is the second parameter of the Index function written as Match function. Here Match function will search the name in A2 cell of sheet2 and then look into the array in sheet1 from A2:A7 to get the position number (here also you have to lock the cells using dollar sign). The third parameter for the Match function is given as 0 to get an exact match. Once you enter the formula, you can copy down the formula. The last person Raj is not there so you will get the N/A error. In the previous example of the Index Match, we have used the same array in both Index and Match functions. But here we have used different arrays because in Index we have used the column (D2:07) from where we want the answer, physics score. In match function, we have used the table array we want to compare (A2:A7), the name. Index Match syntax is in the reverse order of Vlookup function. In Vlookup we are specifying the column data to fetch at the end whereas in Index Match it is stated at the beginning. So in plain English, the format will be Index ( Data you want if it matches, Match (lookup value, Where to look). Another way to remember the Index Match is Match function is similar to Vlookup. In Vlookup you have to give the lookup value first followed by the lookup array. So if you know the syntax of Vlookup, you can easily learn Index Match. As in the Vlookup, you have to put the dollar sign to lock the cells. If you are comparing two Excel workbooks, then dollar sign will automatically appear. Otherwise, you have put the dollar sign for the row as well as column. To speed things up, you can use the shortcut key F4 to enter the dollar sign.

01 Advantages of using Index Match

Index Match function has several advantages over Vlookup, and the main advantages are listed below. Unlike Vlookup you don't have to find out the column number you want to fetch data in Index Match. In Vlookup you have to give the column number from which you want the data. I have seen so many instances where we have used the wrong column number, and the entire calculation went wrong. The Second advantage is if you insert a column in between the data you are looking for, column number will change accordingly and still give you the correct result. In Vlookup if you insert a column in the table array you have used in the formula it will break or give wrong results based on where you are inserting the column. In other words, Index Match is a dynamic function which will adjust itself even after inserting or deleting the column. The Third advantage is you can do a left lookup also. In Vlookup your data should be arranged to the right from the column data you are matching. But here we don't have to worry about that; you can select data from whichever column you want. The fourth advantage so many Vlookups in different columns will slow down the calculations in Excel. This is because Vlookup selects the entire column even if you don't use it but in Index Match we are giving only the column from which you want to fetch data. So this will speed up the calculation. Fifth attraction is Index Match can be used as Vlookup as well as Hlookup formula.

01 Limitations

These are some of the disadvantages of Index Match. Since this is a combination formula the Syntax for the formula is not written anywhere in the help menu of Excel. So the only way to learn this formula is to memorize the syntax. In plain English, if you want a shortcut for the syntax it will be like this.

Index ( data column you want the data from, Match (lookup value, where you want to search in a column))

Unlike Vlookup first you have to specify the column you want to retrieve the data. In Vlookup this is given as the third parameter. So to remember easily first, you have to specify the column you want after the Index function is written. Then it is same like Vlookup function, the lookup value and then the lookup array.

Also, you have to remember Index Match is not case sensitive it will treat World or WORLD as same. Also If there are multiple values, it will fetch the first value from the lot even if there are duplicate values. To fetch multiple values, you have to do the same tricks we have applied for the Vlookup like concatenating different columns to make it unique.

01 Index Match as Hlookup
As I have just mentioned in the previous section, you can use Index Match as a Hlookup function. Keep in mind whenever possible you must eliminate putting data horizontally as Excel is built for doing things vertically. But if some situation arises you have to use this formula. For using this formula as Hlookup, everything is same except you will feed the arrays horizontally. Example: Open the file Chapter 7.3 – IndexMatchasHlookup.xlsx Let us go through the same example file we have used. I have arranged the data horizontally. Instead of names written in A column, now it is in the first row. Sheet1 image [example] Sheet2 image [example]

And in the second sheet also the names are arranged in the first row instead of first column. Now ente the formula =INDEX(Sheet1!$B$4:$G$4,MATCH(Sheet2!B1,Sheet1!$B$1:$G$1,0)) in B2 cell and copy across to F2 cell.The only difference with the previous formula is the arrays are selected horizontally instead of vertically. Rest everything is same. So by learning a single formula, you can write Vertical as well as Horizontal lookup with slight adjustments in the range you select.

01 Index Match Match

This formula is used for doing the matrix type lookup we have learned using Vlookup Hlookup and Vlookup Match. But this combination is more useful than those two. Hope you remember the syntax of Index function. We will go through the same once more. Syntax: INDEX(array, row_num, [column_num]) Here we have the array as the first parameter, then the row number followed by column number. Now in the Index Match function, we have not used the third parameter, we have only used second parameter. In Index Match we have used Match function to fetch the row number. So to get a matrix type lookup you have to replace both the second and third parameter of the Index function with Match function. First Match function for matching the row and second for matching the column. So in plain English, it will be like this. Index(Where to look for, Match row numbers, Match column numbers) Example: Open the file Chapter 7.4 - IndexMatchMatch.xlsx [example] Enter this formula in C12 cell =INDEX(B2:F7,MATCH(C10,A2:A7,0),MATCH(C11,B1:F1,0)). We have given the first parameter B2:F7 from the array we want to fetch the data. First Match function will check the value entered in C9 cell Hary with the cells A2:A7. Since Hary is the second position in the array, it will return 2 and this will be the row number of Index function. Now from the second Match function will check the value entered in C10 cell which is Physics with the array of cells B1:F1 and will fetch 3. This will be used as column number in the Index function third parameter. So once the Match function has found out the row number and column number, Index function will look like this. Index(B2:F7,2,3). This means it will fetch the second cell data from B2 and third column from B2 which is 71
06 Index Match for Case sensitive lookups

Now like Vlookup we have to use the Exact function to do a case-sensitive lookup. Keep in mind if there are two cells with the same value then it will retrieve the first value. If you want to make it unique, you have to concatenate with another cell. A case-sensitive lookup with Index Match structure will look like this. {=INDEX(data column you want ,MATCH(TRUE,EXACT(lookup value, lookup column),0))} Example: Open the file Chapter 7.5 - IndexMatchCaseSensitive.xlsx This is the same example we have used earlier. Here we want to find out what score some students have got in Finance, Marketing, Physics, Mathematics and Computer science. We have gone through this example before, but here there are two differences. In the Match part of the formula we have nested the formula Exact and converted to an array formula by pressing Ctrl + Shift + Enter. You will see a curly bracket once it is converted to array formula. [example] Now enter the formula in D10 cell =INDEX(B2:B7,MATCH(TRUE,EXACT(C10, A2:A7),0)) and instead of pressing Enter press Ctrl + Shift + Enter to convert this formula to array formula. After converting formula will look like this ={INDEX(B2:B7,MATCH(TRUE,EXACT(C10,A2:A7),0))} We will examine the array formula used for the student John. We will go through the inner formula Exact. Exact is used to compare two cells and if the case and word match it will return true or else it will return false. For Example, EXACT("jain","jain") will return True and EXACT("jain","JalN") will return False. By default, Exact will compare one cell with another but here we are comparing one cell C10 to an array of cells A2:A7. That is why we are changing the formula to an array. So when the Exact formula is executed it will be like this {TRUE;FALSE;FALSE;FALSE;FALSE;FALSE} an array of True and False. In the Match function first argument we have given True and it will check the position of the TRUE in this array. Here True is the first position and Match will return this as the row number to Index function. So for John, we will get the row number one and Index function will fetch the data from the row specified. Now in the C13 cell, you have entered the name ancy in small case and you got the error because of the case difference.

× DATE: Thu, Feb 06, 2020
× TIME: 9:38:44 pm
× BEHAVIOR:
on Twitter or sign-up for my monthly newsletter: