VLOOKUP MATCH – Solution for VLOOKUP REF Error
The article “VLOOKUP REF Error – Causes and Solutions” explains the possibility of a REF error, or there is no REF error, but the resulting data is incorrect. The following article explains how to prevent these two problems.
The First Case
For example, there is data as shown below. How much is the total bill for all ordered drinks with the “Venti” size?
The Formula
=VLOOKUP(F4,$A$2:$D$13,4,FALSE)
The price for the “Venti” size is in the fourth column of table_array, so the col_index_num argument is number 4
The Result
What if you delete the column B (Tall Price)?
REF Error immediately appears in the column G. Is there any solution to prevent it, even though the column B “Tall Price” is removed the total bill for the “Venti” size could be known.
The solution is using VLOOKUP MATCH Formula.
The Modified Formula
=VLOOKUP(F4,$A$2:$D$13,MATCH("Venti",$A$1:$D$1,0),FALSE)
The MATCH function fills the col_index_num argument; the role is to find the “Venti” word position in range A1:D1.
What if you delete the column B (Tall Price)?
No REF error appears. Why? When the array dimension changes because of a delete operation, the MATCH function result changes too. When a “Tall Price” column exists the MATCH function returns 4, when you delete the “Tall Price” column the MATCH function returns 3.
The Second Case
For example, there is data like the picture below, the total bill for all “Tall” size drinks is $ 28.20.
What if you delete the column B (Tall Price)?
There is no error but the total bill changes to $33.35, it turns out the price of drinks changes from the “Venti” price size to the”Grande” price size. No error, but the formula result is wrong. It is a BIG problem.
How to prevent the wrong result? Of course, by displaying an error if the “Tall Price” column is deleted. The solution is using VLOOKUP MATCH Formula.
The Modified Formula
=VLOOKUP(F4,$A$2:$D$13,MATCH("Tall",$A$1:$D$1,0),FALSE)
The MATCH function occupies the col_index_num argument. The role is to find the “Tall” price column position.
If column B “Tall Price” is deleted the result as shown below.
NA error appears informing there is no price for the “Tall” size.
VLOOKUP MATCH – Solution for Dynamic col_index_num
For example, there is data as shown below. How do you calculate the total bill?
Unlike the previous case which only calculates the bills for one drink size. In the example above, the size of the ordered drink is different, so if you use the VLOOKUP function, the col_index_num argument could be numbers 2,3 or 4. If this is done manually one by one, it will be very time-consuming.
The solution is to use the MATCH function to find the drink price position according to their size.
The Formula
=VLOOKUP(F4,$A$2:$D$13,MATCH(G4,$A$1:$D$1,0),FALSE)
The Result
The other alternative for dynamic col_index_num is to use the INDEX MATCH MATCH formula, both return the same result.
Which one is your choice? The VLOOKUP MATCH Formula or the INDEX MATCH MATCH Formula?
