The Fantastic Excel Index and Match Functions. Peas in a pod.
Free Microsoft Office Tutorials
Add Excel index and match functions together and the results are synergistic.
This information applies to Excel 2003 /07/10.
The workbook used for this demonstration is Microsoft Excel 2003.
A very effective alternative to Vlookup and Hlookup is the Index and Match functions combined. With Vlookup and Hlookup you have to deal with just your first column for your criteria or “Look up values”. With Index and Match functions combined you can reference any column or row for your “look up values”
This is a practical tutorial showing how to look up and fill in staff details. You can also use this for a lookup for a phone list if you wish. With a little bit of validation we can make this lookup dynamic.
In this instance we use the Index function to look up the column and the match function to find the row reference.
Watch this short video tutorial on how to use the Excel Index and Match Functions 1
Watch this short video tutorial on how to use the Excel Index and Match Functions 2
You can download a sample workbook.
We use the Index function for the column reference and the Match function to find the row reference. Look at the break down of the formula.
The syntax for the INDEX function is:
The syntax for the MATCH function is:
=MATCH(lookup_value, lookup_array, [match_type])
Here is the Index function =INDEX(B6:F11,3,3)
Now the Match function =MATCH(C13,B6:B11,0)
Here we see the Index and Match formula broken down
Some data validation to make the process automatic
Put your look up values into a drop down list
Let's take this on step further. Please refer to the second video tutorial.
The second video shows how to use Index with 2 match functions to lookup with double criteria. We here use 2 drop down validation lists 1 for each match function.
This is a great trick to remember.
I have used 2 named ranges here to reference the rows and columns. These could be dynamic ranges to allow the data to expand and contract.
Here we change things a little bit. I have used the Index function to reference the array and 2 match functions for the row and column look up.
Watch the video and download the workbook. The applications for this are endless. Try this in your next appropriate project where you would have used then Vlookup function.
Let me know your comments.
Excel Index Match Functions are Great Dance Partners