

You’ve probably heard of the VLOOKUP formula in Excel, haven’t you? The VLOOKUP formula searches for a value in a column. Once found it returns another value from the same row. A combination of INDEX and MATCH serves the same purpose. It works slightly different and has therefore some advantages and disadvantages towards VLOOKUP.
First things first: When we say VLOOKUP in this article, we also refer to the HLOOKUP formula in Excel. It works the same way only with horizontal rows instead of vertical columns. This also applies for the INDEX and MATCH formula combination because it works the same way horizontally and vertically.
Contents
As INDEX and MATCH are two separate formulas, we take a look at them separately before putting them together.
The INDEX formula returns the n-th value from an array of cells.
Let’s say we got a range of 4 cells, for example D3:D6 (blue range in the picture above). We want to know the value of cell D5. As D5 is the 3rd value in this range, we type the following formula:
=INDEX(D3:D6;3)
The first part (number one on the picture), defines the array of cells which we want to get the return value from. The second part of the formula says, that we want to get the value of the third cell in this range.
With MATCH, you can search for a value within a range of cells. Once found, MATCH returns the location of the first occurrence.
An example: We got a cell range B3 to B6 with car brands (the red cell area in the picture above). We want to search for the brand “BMW” within this cell range. The result should be the number of the cell. Counting starts with the first cell of the range.
=MATCH("BMW",B3:B6,0)
So we search for “BMW” (first part of the formula, number 2 in the picture above), within the range B3 to B6 (second part of the formula, number 3 in the picture above). The third and last part of the formula is a little bit tricky:
In our case we search for the exact name “BMW” so that we have to fill in 0 as the last part of the formula.
Excel too slow? Speed it up. Get the book now!
Tired of waiting for Excel? Use the 30 best methods described in this book to speed up Excel calculations!
Learn more or get it on Amazon!
How to use the INDEX-MATCH formula combination in Excel
We now know how to use INDEX and MATCH separately so that we can combine them. In our example, we search for BMW in the first column (column B) and want to get it’s price from column D.
The formula has 4 relevant parts (the numbers are corresponding to the picture above):
The complete formula looks like this:
=INDEX(D3:D6,MATCH("BMW",B3:B6,0))
A combination of the two formulas INDEX and MATCH has some advantages and disadvantages towards VLOOKUP:
If you want to know more about when to use VLOOKUP, SUMIFS or INDEX and MATCH, please have a look at this article.
Method 4: The INDEX/MATCH/MATCH combination.
Above you’ve learned the simple version of the INDEX formula in Excel. But there is an advanced way to use it too: You can not only provide one dimension for a lookup, but also a second one. That way, you can create a 2 way- or 2D lookup.
Instead of just a simple INDEX/MATCH combination, you can add one more dimension with the second MATCH. The formula looks like this:
=INDEX(B2:E8,MATCH(C11,B2:B8,0),MATCH(C12,B2:E2,0))
The INDEX formula has 3 parts here: The complete cell range (B2 to E8) and two MATCH formulas.
Both MATCH formulas return the number of the cell in with the search term the first time comes up.
Do you want to know more about the 2D lookup in Excel? Please refer to this article.
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
Comments 3
Abhimanu mallik
really your teaching method is very excellent …
Henrik Schiffner
Thank you very much!
ramit basu
its really helpful want to know excel vba