In this article we will talk about the INDEX and MATCH formulas, which are two useful lookup functions when others (such as VLOOKUP) reach their limits. Do you like examples? I do, let’s be practical:
Example
The report below, which I know it’s very complicated, hard to understand (not). We see that the Sales box is empty, and we want there to show us, based on the month written just below, the amount of sales.
INDEX Function
For this we use the INDEX function, let’s see exactly how the syntax looks like.
=INDEX(array, row_num, [column_num])
- Array: cell area where we search
- Row_num: row number that we want to return
- Column_num: they return the column number (optional in cases where the cell has a single column)
Back to example
Depending on the value entered in E1, we want to return the corresponding sales amount. If you read the description above you probably agree with me that when we write INDEX formula:
- Array: are values from A2 to A7, namely sales numbers
- Row_num: will be equal to what we write in E1
- Column_num: we are not interested in this case, the new cell is selected by a single column
Our formula will be:
=INDEX(A2:A7,E1)
Let’s complicate things
Now that you understand the basic idea about how INDEX formula works, let’s see an example a little bit more complex. Our company has grown and now sells products in three areas: Oradea, Cluj and Bucharest. Now the report looks like this:
* Before you scroll below, think how you would proceed.
Now, I want the formula to show us the amount of sales based on the month and region of each sales.
MATCH function
Perhaps you have realized that the INDEX function will not work by itself if you will give as arguments February (month) and Oradea (region). This function needs numbers. So, we still need to look for a function to find regions and mouths, in order to return us a number. This function is called MATCH and here is how it looks:
=MATCH(lookup_value, lookup_array,[match_type])
- lookup_value: the value we are looking for
- lookup_array: cell area in which we are looking for
- match_type (optional): type search, more than, less than, equal
To see how much the value of G1 A1: C2 we have:
=MATCH(G1,A1:C1,0)
To see how much the value of G2 D2: D7 we
=MATCH(G2,D2:D7,0)
INDEX+MATCH
To find the amount of sales per month and region we use INDEX and MATCH in combination. Cut to the chase, here`s formula:
=INDEX(A2:C7,MATCH(G2,D2:D7,0),MATCH(G1,A1:C1,0))
Download the example
Here you can download the file I’ve been working on.
VLOOKUP Relationship
Some of you probably have faced this, the VLOOKUP formula knows to look only in the right of the table array, and that can be problematic in some cases. Luckily, using INDEX and MATCH formulas you can solve this problem with these method and you put VLOOKUP to rest for a moment.
Your turn
Tell me in the comments section below in what other situations we can use the INDEX and MATCH formulas.