INDEX MATCH Formula
You can use the INDEX function and the MATCH function together instead of the VLOOKUP function
For basic VLOOKUP formula, read the article below
For example, there is data as shown below.
How much you have to pay for all tall orders?
The first step to calculate all total bills is to find the price of each drink. Usually, the VLOOKUP function is used to find the prices, for now, it will use the INDEX MATCH formula.
Let’s start with the MATCH function first, to find the position of each drink name in the price table.
=MATCH(F5,$A$2:$A$13,0)
Why does the lookup_array argument use the absolute reference? Read the following article for the explanation:
After the position of each drink name is known, it’s time to retrieve the price of each drink. Drink prices for all sizes are in range B2:D13, by using the results of the MATCH function in column_num argument, the INDEX function can retrieve the price for each drink.
=INDEX($B$2:$D$13,MATCH(F4,$A$2:$A$13,0),1)
Use the SUMPRODUCT function to calculate the total bill in cell H9.
The array argument of the INDEX function uses absolute reference; the reason is the same as the lookup_array argument of the MATCH function using an absolute reference.
All orders have the same size, Tall, so the column_num argument of the INDEX function is filled with number 1, the column position in range B2:D13 which contains the price of drinks for Tall size.
What if the drink order has different sizes as shown below?
It’s time to use the INDEX MATCH MATCH formula.
INDEX MATCH MATCH Formula
If the size of the ordered drink varies, then you need one more MATCH function to determine the position of the size of the drink ordered. The additional MATCH function will fill the column_num argument of the INDEX function.
=INDEX($B$2:$D$13,MATCH(F4,$A$2:$A$13,0),MATCH(G4,$B$1:$D$1,0))
The lookup_value argument of the MATCH function point to cell G4, containing the size of the drink ordered — the lookup_array argument point to range B1:D1 which contains information of all drink sizes and uses an absolute reference.
The result is as shown below.
The Advantage of the INDEX MATCH Formula Compared to the VLOOKUP Function
Lookup direction after lookup_value found
The VLOOKUP limitation is just looked to the right column to retrieve the result value. No VLOOKUP to the left, instead the INDEX MATCH formula can do lookup to the right or lookup to the left.
This limitation occurs because the VLOOKUP lookup_value requires to be positioned in the first column of table_array while INDEX MATCH lookup_value can be positioned anywhere.
Number of match type
The VLOOKUP function has two match type, Exact (FALSE) and Approximate (TRUE). If using approximate match type, the first column of table_array must be sorted in ascending order.
The INDEX MATCH formula has three match type, Exact (0), Less than approximate (1) and greater than approximate (-1).
If you use less than approximate (1) the lookup_array argument must be sorted in ascending order, whereas if you use a greater than approximate (-1) lookup_array must be sorted in descending order.
The INDEX MATCH formula has more choice of match type, more flexible with various data conditions.
Read the following article for a detailed explanation of VLOOKUP limitations and solutions
Practice File Download
Download File
