I go to parties and all I hear people saying to each other that using Index and Match is better than VLookup! Obviously these are not the parties i used to go when i was a young and viberent man. having no clue about any of teh terms they used I would shake my head in agreement and say "Yes indeed! I totally agree with you!"
I knew what Vlookup was all about but had no idea what the other two cousions of Vlookup people were talking about.
So, I decided to do a research on these and would like to share my newly found knowledge and hope you benifit from them.
Well let's start off with something that I already know about and that is Vlookup!
Vlookup function is a very useful function when you want to retreive data from a very long list that may be on the same sheet or another sheet or even another workbook. Let me explain by giving you a simple and realistic example.
Let's say that you have three sheets namly Invoice, Customers and Products. On the Invoice sheet you want to put the Customer ID and magically want the Title, First Name, Last Name, Full Address to appear on the other cells. Or may be Enter the product Code and again magically get all the relevant product information such as Unit Price, Product description, etc... to appear for you.
So How and where does the all the magical information come from? If you are as smart as I am you'd be able to guess that the information comes from the other sheets namely Customers and Products. So how does it do what it does and more importantly where do you buy this Magic Wand that helps us to make our life so easy. I don't know about you but my genearl experience of life is that Nothing comes easy in life. You have to work hard to get what you want in life. Now I must have got you really concerend and you must have started thinking that I'll be asking you to go out on this epic adventure, like Harry Potter or Lord Of the Ring in search for our "Magic Wand" !
The good news is that it is not as intense as you might expect it to be.
Please see teh three sreen shots below of my scenario:
So on my Invoice sheet when I type in C002 in cell J1 I want all the relevant information to be filled in for that customer. Following are the steps to achive this.
Step 1: On the Invoice sheet I click on J2 and start typing: =vlookup(
automatically a nice little box comes up and I see Lookup Value, Table_Array, Col_Index_Num, Range_Lookup
So what do these mean and, more importantly, when do I get to use the Magic wand?
Lookup Value is the cell or the text that needs to be referred to.
Table Array is the entire table where the information is situated.
Col_Index_Num is the column number in the table array where you want the data to be extracted from
Range Lookup is either 0 or 1 depending on if you want an exact match or an approximate match.
Let's put this in contecxt with our example:
In my Invoice sheet if I type in e.g. C002 on cell J1
J1 will be the Lookup value as J2 will always be referring to J1 to see what value is in J1. Just to let you know taht you can also enter a specific text surrounded by the inverted commas if you are likely to be searching ofr teh same text all teh time. That is not teh case in our example.
Table array in our case is NOT on the same sheet so we will have to click on the Customers Sheets and select teh entire table (A1:G3)
Column Index Num is going to be 2 as the Title in our table Array is situated on the second Column.
Range Lookup will be 0 or you can type False which means that we are looking sor an Exact match. This will ensure that if the entery in the Invoice sheet on cell J1 has not been enterted exactly same as in teh table array then instead of working out the closest match it will come up with an error.
So to summarize all the above arguments of the Vlookup function on Invoice sheet in Cell J2 we willl type in
=Vlookup(J1, Customers!A1:G3, 2,0)
Note that you don't have to type "Customers!" because as soon as you click on the customers sheet this should be entered for you automatically.
You will repeat the steps for all the other cells in Invoice sheet to get the details for rest of the fields such as First name, Surname, Address 1, Address 2, Post Code.
Those of you who understand what dollars do in Formulae and functions you can even put them dollars for J1 and A1:G3 in the original Vlookup on the J2 Cell and then simply copy and paste the cell down to J3: J7. All you’ll have to change is the Column Index Number depending on the position of the headings in our table array.
Similar can be done for the Products information on the invoice sheet.
I hope this all makes sense so far. Now the question is if we can get the result we need from Vlookup then what is all the fuss about the other two functions called Index and Match?
Well as with all great things Vlookup has it’s drawback. And the drawback is quite a significant one. Notice that Customer ID is the Lookup value in our Vlookup function. And coincidently in our table array on the customers sheet Customer ID happens to be our first column. Well what that means is that had our first column in our Customer sheet been anything else Vlookup would not have worked. The problem we have is that Vlookup can only look at the left most (first) column in the table array and match that with the Lookup Value and then provide you the answer. In our case this seemed to work perfectly but then there are cases when it doesn’t work at all and Vlookup’s cousins Match and Index come to its rescue.
Let’s see the example where Vlookup will not work:
Let’s say that you knew the name and wanted to find the customer ID! That would be a problem! Because Vlookup will still be looking for first column i.e. Customer ID whereas we wanted to actually to look into the Second column to find us the data in the first column.
Those of you who understand what dollars do in Formulae and functions you can even put them dollars for J1 and A1:G3 in the original Vlookup on the J2 Cell and then simply copy and paste the cell down to J3: J7. All you’ll have to change is the Column Index Number depending on the position of the headings in our table array.
Similar can be done for the Products information on the invoice sheet.
I hope this all makes sense so far. Now the question is if we can get the result we need from Vlookup then what is all the fuss about the other two functions called Index and Match?
Well as with all great things Vlookup has it’s drawback. And the drawback is quite a significant one. Notice that Customer ID is the Lookup value in our Vlookup function. And coincidently in our table array on the customers sheet Customer ID happens to be our first column. Well what that means is that had our first column in our Customer sheet been anything else Vlookup would not have worked. The problem we have is that Vlookup can only look at the left most (first) column in the table array and match that with the Lookup Value and then provide you the answer. In our case this seemed to work perfectly but then there are cases when it doesn’t work at all and Vlookup’s cousins Match and Index come to its rescue.
Let’s see the example where Vlookup will not work:
Let’s say that you knew the name and wanted to find the customer ID! That would be a problem! Because Vlookup will still be looking for first column i.e. Customer ID whereas we wanted to actually to look into the Second column to find us the data in the first column.
So how do we achieve this?
MATCH(lookup_value,lookup_array,match_type)
Lookup_value is the value you use to find the value you want in a table.
Lookup_array is a contiguous range of cells containing possible lookup values.
Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.
If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order.
If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.
If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order.
If match_type is omitted, it is assumed to be 1.
So in our example we will type:
=MATCH(B6,B2:B3,0)
Notice that for the lookup array must be single column list. So if we had chosen A2:C3 you’ll get an error.
So the result of the Match function will be 2 because what Match tells you is the Row position of the entry in our Lookup value cell.
How helpful is this? Well not very much! So has Match Function disappointed us? Well Not really. At least it is telling us which Row number
Let’s See what Index Function can do for us.
=INDEX(data range, row number, column number)
Data range is the where you are looking into
Row Number is ,well, the row number
Column Number is well obviously the column number.
In our example the data range is easy to find that is the entire table. (A2:C3)
Column number is also easy to find as we know that we need the Customer ID to be extracted and that is in Column 1 in our data range , therefore, 1.
Although Row number in our example is not that difficult to find as we know that it is 2 but then the problem is that if we manually type 2 there the name we entered in cell B6 which is currently Mary Jones to john Smith who happens to be on row 1 unfortunately the result on cell B7 will not update as it will still be looking for Row 2 whereas the result should be coming from row 1.
So we need something in the Index Function that will automatically calculate the row number without us having to change it manually all the time. In addition to that if you have 20,000 rows of data would you manually want to search and type the row number yourself? I am hoping your answer is NO!
So what is the solution. That is where these Index and Match functions work in conjunction to get your desired result.
So your enter the Match function inside the Index function.
In our example the function would be something like this:
=INDEX(A2:C3,MATCH(B6,B2:B3,0),1)
So Match function tells the Index Function which row needs to be retrieved in order to get the correct result.
I hope this makes things clear for you!
Now for the BIGGEST question of All!! Where is that Blooming Magic Wand???? Well you don’t need one by understanding whatever is mentioned above you would be a proud owner of THREE Magic Wands: VLOOKUP, MATCH AND INDEX!!
MATCH(lookup_value,lookup_array,match_type)
Lookup_value is the value you use to find the value you want in a table.
Lookup_array is a contiguous range of cells containing possible lookup values.
Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.
If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order.
If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.
If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order.
If match_type is omitted, it is assumed to be 1.
So in our example we will type:
=MATCH(B6,B2:B3,0)
Notice that for the lookup array must be single column list. So if we had chosen A2:C3 you’ll get an error.
So the result of the Match function will be 2 because what Match tells you is the Row position of the entry in our Lookup value cell.
How helpful is this? Well not very much! So has Match Function disappointed us? Well Not really. At least it is telling us which Row number
Let’s See what Index Function can do for us.
=INDEX(data range, row number, column number)
Data range is the where you are looking into
Row Number is ,well, the row number
Column Number is well obviously the column number.
In our example the data range is easy to find that is the entire table. (A2:C3)
Column number is also easy to find as we know that we need the Customer ID to be extracted and that is in Column 1 in our data range , therefore, 1.
Although Row number in our example is not that difficult to find as we know that it is 2 but then the problem is that if we manually type 2 there the name we entered in cell B6 which is currently Mary Jones to john Smith who happens to be on row 1 unfortunately the result on cell B7 will not update as it will still be looking for Row 2 whereas the result should be coming from row 1.
So we need something in the Index Function that will automatically calculate the row number without us having to change it manually all the time. In addition to that if you have 20,000 rows of data would you manually want to search and type the row number yourself? I am hoping your answer is NO!
So what is the solution. That is where these Index and Match functions work in conjunction to get your desired result.
So your enter the Match function inside the Index function.
In our example the function would be something like this:
=INDEX(A2:C3,MATCH(B6,B2:B3,0),1)
So Match function tells the Index Function which row needs to be retrieved in order to get the correct result.
I hope this makes things clear for you!
Now for the BIGGEST question of All!! Where is that Blooming Magic Wand???? Well you don’t need one by understanding whatever is mentioned above you would be a proud owner of THREE Magic Wands: VLOOKUP, MATCH AND INDEX!!
I owe my newly founded knowledge to a company called Mouse Training whose free course manuals I downloaded and learnt the skills.
If you would like to download any of the MS Office step by step guides for Office 2003, 2007 and even 2010 then please feel free to download them from Mouse Training Free Manuals Link!
Well I am one of the proud MOUSE of the MOUSE training to get Trained!;)
Raj
