Why You Should Use INDEX & MATCH Instead of VLOOKUP
Why is INDEX & MATCH so much better than VLOOKUP?
Hi, we are Happy
We are leading a movement to create happy, empowered and productive workplaces.
How can we help you and your people to find joy in at least 80% of your work?
For many users, VLOOKUP is a useful function that allows you to lookup data from a specific data table; the lookup table can be on the same worksheet, in a different worksheet, in the same workbook or even a different workbook entirely.
In a VLOOKUP function, Excel will only look from the left to right of the lookup table for either an exact match or the closest value and then return the value in the Nth column from that row in the lookup table.
But there are three circumstances where VLOOKUP doesn’t work or isn’t the best solution:
- VLOOKUP can only look from left to right
- VLOOKUP is prone to error, especially in big spreadsheets
- VLOOKUP slows down big spreadsheets
You may find that the VLOOKUP function is returning the value with an error or incorrect value. In my experience, there are six main reasons for this and that is where INDEX and MATCH come in useful:
- Data is not sorted properly
- The value sought comes before the first range
- No matching data found in the lookup table
- Data type mismatch
- Extraneous spaces
- Special characters
INDEX and MATCH works very well if your lookup data is not in the first column, or you want to look to the left of the lookup data, rather than to the right, which is all VLOOKUP can do.
What the MATCH function does
The MATCH function allows you to find the position of the value in a list either in a column or row of data.
The MATCH function can either look in a column of data and return the row number of a value that you specify, or look in a row of data and return the column number of a value that you specify.
The MATCH function has the following syntax: =MATCH(lookup_value, lookup_array, match_type)
- Lookup_value is the value you want to find in the list.
- Lookup_array is the range of cells that contain the list.
- Match_type is an optional value that defines the type of match you are looking for. It can have three possible values: -1, 0 and 1.
A match_type value of 1 is used when your list is sorted in ascending order (smallest to largest). A match_type value of 0 means your list doesn’t need to be sorted. A match_type value of -1 is used when your list is sorted in descending order (largest to smallest).
Ed’s top tip: Always set the correct match_type when using the MATCH function rather than leaving it blank, because with a blank value, the function will assume a value of 1 and the function won’t always return the results you expected.
What the INDEX function does
The INDEX function allows you to either look in a column of data and return a value from the row that you specify or look in a row of data and return a value from the column that you specify.
There are two different versions of the INDEX function, the Array form and the Reference form.
The INDEX function has the following syntax: =INDEX(array, row_num, [column_num])
- array is the range of cells you want INDEX to look in, in our example the array will be A3:F15.
- row_num is the row you want INDEX to look in. This can be a number or a MATCH function to find the number.
- column_num is an optional value that column you want INDEX to look in. You only need it if you want to look in a column other than the first column in the table range you specified. This can be a number or a MATCH function to find the number.
Putting INDEX and MATCH together
Now that we understand what the INDEX and MATCH functions do on their own, let’s put them together to create an INDEX and MATCH function instead of using VLOOKUP.
Here is how we combine our new formulas: =INDEX(array, row_num,MATCH(lookup_value, lookup_array, match_type))
Take a look at the file below:
We want to find how much Paper was sold in April. We assume that each name appears only once in the list, and there is no duplicated data in either the column or row headings.
To do this, we have used this formula in cell B23: =INDEX(A3:F15,MATCH(B17,$A$3:$A$15,0),MATCH(B18,$A$3:$F$3,0))
This formula says “Find the row that contains Apr in Column A and find the column that contains Paper in Row 3, and then get the value in the 5th row and 3rd column of the table. If you don’t find either Apr or Paper, then return an error”.
So, VLOOKUP isn’t always the best choice when trying to find values in a table based on a lookup value, because
- VLOOKUP slows down processing, especially in big spreadsheets
- INDEX MATCH can do something VLOOKUP can’t do (look to the left in a table as well as the right)
- INDEX MATCH combination is less prone to error
Why not sign up to our newsletter?
Sign up to our monthly newsletter, full of tips, tricks and news to help you to be happier and more productive at work.
Ed is a Microsoft Certified Trainer (MCT) and has worked at Happy for over 12 years. Although he trains the full suite of MS Office and non-Office packages at all levels, his favourite package is Excel. A previous winner of the prestigious Learning and Performance Institute's 'Learning Professional of the Year' award, as well as other training accolades, he is also the IT Helpline Manager.
Testimonials from happy Customers
Siobhan Brennan-Robson7 days ago
Ebiji was an excellent teacher. I will be keeping her on speed dial.
Sophie Allen7 days ago
the initial invitation didn't come to all participants so I wasnt sure if the training was confirmed
Trusted Customer8 days ago
Amazing work! Honestly, I have had such a pleasant experience with Happy whether it be online or in their offices. Hopefully, in the future, I get to have more sessions w...
Trusted Customer10 days ago
I found the Office 365 training really useful. It consolidated my self-taught learning and showed me some new things that will make my life easier as well as doing some t...
Trusted Customer10 days ago
Found the session interactive and engaging throughout. Range of tools shared.
Helen Twigg11 days ago
Really enjoyed the Liberating Structures Online Immersion Workshop. High energy that kept you involved all day and have been able to use the learning immediately. Woul...
Ruth Gibbins11 days ago
Great facilitation, very interactive and organsied
Emma Willcox11 days ago
Great course, with lots of opportunity to practice techniques and not just learn about them. The day is packed full of content, so be ready to embrace the fast pace and t...
Judy Duff11 days ago
Happy are a creative, unique, relevant and modern-day approachers of workshop delivery and content.
Trusted Customer13 days ago
Excellent course and facilitator :) The course had good interactive sessions and well thought out content. It was made relevant to our roles and the trainer was supportiv...
Scott Haines14 days ago
I thought the training was excellent. It was really enjoyable and engaging and gave me lots of ideas to take away.
Toks Kinoshi14 days ago
An excellent session led by Henry where I learnt some new, refreshing techniques