The Wrong Cell Reference
The wrong cell reference is the most common error when using the VLOOKUP function to look up for multiple data at once. For example, there is data like the picture below, how much you have to pay for all tall orders?
To calculate the total bill the first step is to find the price of each drink. Place the cursor in cell H4 and use the VLOOKUP function to find the price of Caffe Mocha.
=VLOOKUP(F4,A2:D13,2,FALSE)
The result is $3.45
No need to write the formula one by one for each drink. Simply copy (CTRL+C) the formula in cell H4 then paste (CTRL+V) in range H5:H8. The result is as shown below.
Why does the #N/A error appear? Let’s check the formula for each drink one by one.
The formula for “Caffe Mocha” price, the lookup_value argument point to cell F4 (RIGHT) and table_array point to range A2:D13 (RIGHT).
The formula for “Caffe Latte” price, the lookup_value argument point to cell F5 (RIGHT) and table_array point to range A3:D14 (WRONG). Table_array shifts 1 row down when copied down, causing “Caffe Latte” in cell A2 outside the table_array range. The shifting table_array is what causes #N/A error.
The formula for “Toasted Graham Latte” price, the lookup_value argument point to cell F6 (RIGHT) and table_array POINT to the range A4:D15 (WRONG), but why is the price right?
The table_array range indeed shifts further and further from its starting point, but the “Toasted Graham Latte” price stays in the table_array range, so even the table_array shifts, as long as lookup_value stays in the table_array range, the VLOOKUP function still returns the correct result.
For the next two drinks price, the table_array range is getting far away from the starting point, but all lookup_value is still in table_array range.
The problem is in the table_array argument when the formula copied down the table_array range should keep pointing to range A2:D13.
The Solution
The solution is to use absolute cell reference to lock the table_array range.
“Caffe Latte” price known and all #N/A errors disappear.
For a more detailed explanation of what is relative cell reference, absolute cell reference and mixed cell reference read the following article:
VLOOKUP NA Error
There are five possibilities for VLOOKUP NA Error. Read the article below to find out the cause and solution for each error.
VLOOKUP REF Error
Two conditions that cause the VLOOKUP REF error to occur, all related to the table_array column. The following article discusses in detail with a solution to prevent it.
VLOOKUP VALUE Error
VLOOKUP VALUE Error occurs because the col_index_num value is zero (0) or a negative number
The image above is an example of VLOOKUP VALUE error because the col_index_num value is 0
VLOOKUP NAME Error
VLOOKUP NAME error rarely happens, except for those who are just learning the VLOOKUP function :). This error occurs because of a typo, it could function name typo or range_lookup value typo, or it could be because the lookup_value is text, written directly in the formula and not enclosed in double quotation marks.
It’s function name typo, VLOOKUP not VLOOKU.
You should type FALSE not FALS.
No double quotes enclosed the lookup_value
VLOOKUP Display a Formula Not the Result
Excel has a feature named Show Formulas. If this feature is active, then all cells containing a formula (without exception) will display the formula instead of the results. This feature activated/deactivated by pressing the CTRL+` (inverse quotes, located above the left tab key).
The image above shows the Show Formulas feature in an active state. Range H4:H8 which contains the VLOOKUP function displays the formula, not the price, likewise cell H10 which contains the SUMPRODUCT function to calculate the total bill.
There are similar conditions like when the Show Formulas feature is active. A cell is showing a formula not the result, even though the Show Formulas feature is not active.
This condition occurs if a cell/range has been formatted into text before the formula is created. All formula created in that cell/range will display the formula instead of the result.
The video below explains the steps to produce the error:
Format range H4:H8 to text, write the formula, and you got the formula, not the result.
Pay attention to the formula in range H4:H8. The lookup_value that should change when the formula copied does not change, a condition of the VLOOKUP function showing the formula, but the Show Formulas feature is not active.
Pay attention to cell H12, the cell contains a formula but does not display the formula, a sign that the Show Formulas feature is not active, although this sign does not always appear.
If cell H12 has been formatted as text too, then the situation will be the same as when the Show Formulas feature is active. Make sure the Show Formulas feature is not active before correcting this problem.
The Solution
The solution is easy. Return H4:H8 cell format from text to number, re-edit the formula in cell H4 then press the ENTER key, do a copy (CTRL+C) in cell H4 then do a paste (CTRL+V) in range H5:H8. Viola, you got the prices 🙂
For a clear explanation, see the video below.
VLOOKUP Results Cannot be Processed
There is a condition that the VLOOKUP function has been created correctly but still produces #N/A error. The cause is lookup_value does not exist in the first column of table_array.
A problem arises when another formula uses the VLOOKUP result. The picture below shows the total bill became NA error because one of the VLOOKUP function returns NA error. What should do to make the overall bill return the actual value instead of NA error?
There are three solutions to solve this problem, the article below explains in detail every solution that exists. Which one is your choice?
Be Careful with the Fourth Argument
The fourth argument is the range_lookup argument. Most formulas that use the VLOOKUP function always use the FALSE value for the fourth argument.
Why must be careful with the fourth argument? Because the range_lookup argument is optional, you can use it or not. If not using it, by default Excel will use the TRUE value even though TRUE value is “rarely used” for range_lookup argument.
To avoid errors, make sure you always use the fourth parameter, so Excel doesn’t use the default value. If you want to look up the exact match data, then use the FALSE value. If you’re going to look up the approximate match data, then use the TRUE value.
