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.
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.
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.
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?
Calculate Number of Days Between Two Dates Excel has a function that calculates the number of days between two dates,…
What is Flash Fill Excel Flash Fill automatically fills your data when Excel detects a pattern in your initial data…
What is Paste Special Paste special is a paste but you can choose the result type. As we all know,…
What is the Excel TEXTJOIN Function? The excel TEXTJOIN function is the best function to concatenate strings in Excel, has…
What is the Excel CONCAT Function? The excel CONCAT function combines all data; it could be a text, a number…
What is the Excel CONCATENATE Function? The excel CONCATENATE function combines text or numbers into a text. CONCATENATE Syntax CONCATENATE(text1,…