Excel's INDEX + MATCH - How to use it // 7 real-world examples & tips

i want to find out the email address for each of our employees and fill this table but notice how the name and email address are in separate cells going downwards excel's index match is a powerful alternative to the vlookup formula in this video let me explain what index match is how to use it with plenty of examples by the end of this video you will be able to confidently use index match formula to answer many business related questions let's jump in this video is sponsored by me i ran a course on index match and various other lookup techniques on skillshare so this video is sponsored by my own course on the skillshare platform i'll talk more about that towards the end of this video imagine you have some data like this and you want to find out who is the employee that joined on a date given in this cell now you could of course scan this list and figure out that it is cane padley but if you would like to write a formula index match would be the perfect fit for this kind of situation you would write this formula like this you'll say index select the column from which you want your output so in this case i want the name and then say match select the input value that you are searching for comma and then select the column where you want this value to be found the last parameter of match is important you want to do an exact match so you need to specify this as zero and close the brackets this is a two-step calculation so what happens is first it will find out where that date is and match will give the position of that date and index will in turn give the name let's see this in action we'll get the result as kaine padley now let's break this down into two steps and then see what each of those formulas are doing so here i wrote the internal portion the match one that says match 20 november 18 in this date column with the exact match that would be last parameter as 0 and this will tell you that k that date 20 no but 2018 is the sixth date so if you count from the top it would be the sixth value the index result would be index of all my names comma six and this will simply take all these names as a list and give you the sixth value in that list which would be k in paddling when you combine these two formulas into one goal we simply call that as index match formula learning about index match is one of those powerful steps in your excel journey once you understand how this works you will look at all your lookups and search related questions in a different light i highly recommend that you master this concept so let's go into in-depth detail of this with few more examples in this example we have a salary and we would like to find out who that employee is let's set up the formula we'll say by index select the name column match select my salary in the salary column and specify 0 as exact match now when you press enter it will look for 88 000 and then try to find that and get the name but since we don't have anybody on 88 000 salary we will get the error hash and name the moment you fix this so for example 88 050 we'll get the answer chessbonnel which corresponds to this cell here with the name chessbonnel what if you want to print a message here when the value is not found you could do it by using the if error function around this so if error all of your index match and then value if error so we'll say not found this will still say chess panel because that salary is a valid one but if i put another number that is not there will get not found let us use index match formula to combine two tables here my employee data is there but this time i have put it in a table and here we have department wise employee bonus information i would like to print the bonus for each of our employees here we can add a column to the table by just typing the word bonus and we will say index of my bonus information now because this formula needs to work all the way through this reference need to be absolute reference so we'll type this and then just select everything press f4 key to convert this into an absolute reference comma match match with the department in the current row so that would be at the rate department comma and then we will select these department values make this also as an absolute reference and say comma 0 and close all the brackets we will get the bonus information returned to here just turn this into percentage format and every employee's bonus is calculated correctly index match is also a perfect formula when you have data that is unstructured or in a different format for example here i have got my data and some names and email addresses are there in a separate list but notice how the name and email address are in separate cells going downwards i want to find out the email address for each of our employees and fill this table we can use index match formula like this we'll start by first finding the position of each name in this list so we could use match formula for that match the name in this list because this will always be from k5 to k26 will select all of that and make it an absolute reference and look for an exact match this will tell you where bar funny is found now bar is the fifth item in this list what we need is bars email address which would be the very next item so it will be sixth item so i can then take this and send it to the index function index of my list this also needs to be absolute reference and instead of the original match we will simply say plus one so find the match go to the next cell close the bracket and we will get bars email address let's fill this down and see what happens it does work but certain employees do not have a matching email address so we can quickly fix this with any fairer formula and there you go our email addresses are all nicely fetched index match also allows you to do multiple condition checks easily here i have got a department level bonus but instead of having one bonus per department we have bonus defined by the employee gender as well so if you are a female in the finance department you get four percent whereas if you are a male in the finance department you get three percent bonus let's see how to set up the match function for this we'll say match and instead of giving any individual lookup value we will look up for one you will understand why we are looking up for one very soon and then for the lookup array we will say this array in the department column in the mapping table here will make that absolute and then open a bracket in the beginning say this is equal to that department in e5 we will then multiply this with one more condition the second condition is on the gender so we'll select the gender column make that one absolute equal to my employee gender we'll close all of this and then do an exact match 0. when you type this match formula you need to press ctrl shift enter to get the correct result if you are using xl 365 you can just press enter and it will still work so we get this result as 2 let's cross check this female in procurement procurement female that is the second item in the list so we get two we don't need the item number we need the corresponding bonus so we'll take this match result and we will then send it to the index of the bonus percentages and close the bracket again press ctrl shift enter we'll get the correct bonus let's fill this down and we will get the bonuses for everybody let's double check this female website is six percent this structure of using multiple conditions with index match is one of the most powerful applications of the match function because it is able to take multiple conditions like this and then process them if you would like to learn more about this style of index match formulas with multiple conditions check the video description for an article that i have written previously that talks about it this time let's understand how to use index match match function to do a two-way lookup imagine we have data like this with departments going down and gender values going across with bonus information printed like this and i want to calculate what should be the bonus for each of our employees here before we write the formula let's first understand another special use of the index function so far we have been using index like this index a range of values and if i say 3 i'll get the third value which would be sales but index can also be used to work with two dimensional data so if i select a range like this and specify that i want the second row second value then i will get the four percent that is here so index when you specify a two dimensional range you can specify both row number and column number you can use this idea to create a index match match formula to calculate our bonus values so we will say index select these numbers make that absolute and then the first match will be at the row level match department in these list of departments with an exact match zero second match would be on the column level so we will match the gender on these two values again making that absolute with an exact match and we will finally close our index function and when you press enter we'll get the bonus values for everybody before you get all too excited about the index match let me present you with an alternative as well you could use the newly introduced x lookup function that is available in excel 365 to do the same thing that index match does but with a shorter syntax so for example we could look up this employee kane padley who joined on that date with x lookup like this x look up look up the date in the date column and get the name values from the name column that is it and you will still get cane badly so what else can we do with these lookup formulas in excel if you would like to learn more about various lookup formulas such as vlookup x lookup filter index match i highly recommend checking out my new course on the skillshare online learning platform this course called lookups for data analysis is a one hour completely online course that teaches you all aspects of lookups for data analysis within excel visit shandu.org lookups that is chandu.org lookups to access my course on the skillshare platform when you go there you'll get one month free access to the skillshare premium learning platform as well so check it out and enjoy my course but also enjoy other courses that are available on the skillshare platform thank you so much i'll see you on the skillshare bye

Channel: Chandoo

Views: 211,086

Keywords: chandoo.org, excel tips and tricks, index match multiple criteria, how index match works, match function, index formula, advanced excel, excel index match tutorial, index match in excel, how to use index match, excel 365, index and match, index match match, matrix lookup, index match, xlookup vs index match, lookup with stacked data, index match advanced examples, why and how to use index match formula, Is index match formula hard to understand, Important excel functions

Id: kly0uPIM4IU

Length: 12min 10sec (730 seconds)

Published: Thu Aug 05 2021