INDEX and MATCH Functions
How to use the INDEX and MATCH Functions in Excel:
Another way to perform a lookup instead of using the VLOOKUP Function is to use the INDEX and MATCH Functions together. We will show the MATCH and INDEX Functions below being used separately and then we will show you how to combine the Functions.
In the above example, we are looking for the Item Grapefruit in column C (C4:C10). Looking at the results in cell H5, we show the Grapefruit to be found in the 6th position.
The formula we used was: =MATCH(H4,C4:C10,0)
In this example, we are looking for how many Grapefruit we had for the year 2019. We know the Grapefruit was found in position 6, so we will look in the 2019 column (E4:E10) to find the value listed in the 6th position. The results returned 10.
The formula we used was: =INDEX(E4:E10,6)
INDEX and MATCH Function Combined:
We are now combining the INDEX and MATCH Function. We want to lookup how many Grapefruit we had in 2019. The MATCH Function looks up the location of Grapefruit in cells (C4:C10). The INDEX Function looks up the number of Grapefruit we had for the year 2019. The INDEX Function is looking in cells (E4:E10) for the value that is located in the 6th position. The returned result is 10 Grapefruit for the year 2019
The formula we used was: =INDEX(E4:E10,MATCH(H4,C4:C10,0))