The trouble with VLookup is that the index for the cross reference has to be on the left hand side of the table. What if it isn’t? Say the item you need to cross reference is in the third column or even the last column. In this case you can use 2 functions in conjuction with each other. They are the Index and the Match function.
You may want to download the exercise files before you start this.
As you can see using VLookup with this would be difficult as the product number is on the right.
- Click in cell G2.
- Type the formula =INDEX(A:A,MATCH(F2,D:D,0)).
Now let’s break down this formula to see how it works.
- The first part of the formula the INDEX part is basically looking at a range and returning the value of whatever number you choose in the secound arguement.
- So for example if you typed in =INDEX(A:A,3) the answer would return Drums because Drums is in the 3rd row of the A:A range.
- Then next part of the function, the Match part, is finding out what row the product number is. So if your formula said =MATCH(F2,D:D,0) the result would be 3. That is because the MATCH function looks at the value in cell F2, then looks down the D:D range to see on what row that number occurs in this case it’s row 3.
- Then when you combine the functions together you use the INDEX function to find the information you want to display. You use the MATCH function to find out which row.
- I hope that all of this makes sense if you want more information on this an intermediate Excel training course may do the trick.
Should you want to see a video about this then check out our Excel Index Match training video on the Computer Tutoring site.
Check out our Index Match tutorial.
Alternatively, if you want to see a cool examle of Index Match using it with multple criteria. Then please check out this Index Match Multiple Criteria tutorial.