All Excel users know how to use VLOOKUP and how much useful it is. But most of the excel users has never used INDEX & MATCH functions in excel. Both have certain features that make it superior to VLOOKUP in many situations.
INDEX MATCH is designed for vertical lookups, which is the task that VLOOKUP easily performs. But if we need to perform a matrix lookup, consider using one of the more powerful Excel lookup formula combinations such as INDEX & MATCH.
The INDEX function can return an item from a specific position in a list.
Syntax: INDEX(array, row_num, [column_num])
For more details on INDEX function.
The MATCH function can return the position of a value in a list.
Syntax: MATCH(lookup_value, lookup_array, [match_type])
For more details on MATCH function.
The INDEX / MATCH functions can be used together, as a flexible and powerful tool for extracting data from a table. Let’s see how using an example:
For our example, I have created a table of products and their year-wise sales figures.
Also, I have created drop-down for selecting Products & Year. Check the below screenshots.
1. First select the product from the drop-down as shown in the below screenshot.
Selecting Product – Laptop
2. Now, select the Year from the drop-down as shown in the below screenshot.
Selecting Year – 2014
3. Now, I have entered the formula in the cell C12,
=INDEX(C$4:$G$6,MATCH(C10,B$4:$B$6,0),MATCH(C11,C$3:$G$3,0))
Where,
Array = Table which contains sales figure data(i.e from C4 to G6)
Row_num = As we know MATCH function gives the position of the value in the list, we have used MATCH function to get the position of selected product LAPTOP in the range B4 to B6.
Column_num = Again, we have used MATCH function to get the position of selected year 2014 in the range C3 to G3.
Formula to find Sales figures for year 2014 and product Laptop
4. Finally, we get the sales figure for Laptop in the year 2014 using INDEX and MATCH function.
Using Index and Match function
You can also download the sample excel workbook.
That’s it!!! Hope you have enjoyed this article and learned how to use this excellent excel features.
For any queries or clarifications related to this article please write me at pareshj@snpinfotech.in or visit www.snpinfotech.in
S&P Infotech Blog 