There is a condition, you have written the VLOOKUP function correctly but still returns an NA error, occurs when lookup_value does not exist in the first column of table_array. No problem, no lookup_value in the price table, but the total bill must show the bill for the ordered drink.
The image below shows an NA error because there is no “Hot Brewed Coffee” in the price table. How is the solution to show the total bill for ordered drinks?
Solution #1, Using IFERROR VLOOKUP Formula
The solution is to add the IFERROR function in front of the VLOOKUP function.
The Formula
=IFERROR(VLOOKUP(F4,$A$2:$D$13,2,FALSE),0)
Fill the value argument with the previous VLOOKUP function and value_if_false argument with zero number, meaning that if an error occurs then it will return zero number.
The Result
The price for “Hot Brewed Coffee” becomes 0, and the total bill appears.
Solution #2, Using IFNA VLOOKUP Formula
The solution is to add the IFNA function in front of the VLOOKUP function. What is the difference between IFERROR and IFNA functions? The IFERROR function will catch all errors including the NA error, whereas IFNA only catches the NA error. So, you can use both functions to catch an NA error
The Formula
=IFNA(VLOOKUP(F4,$A$2:$D$13,2,FALSE),0)
The value argument is for the previous VLOOKUP function, and the value_if_false argument is for number 0.
The Result
The result is the same as solution # 1, the NA error disappears in cell H8, changes with the number 0 and the total bill displays all bills for the ordered drink.
Solution #3, Using IF ISNA VLOOKUP Formula
The solution #3 uses a combination of two functions, IF function and ISNA function. Use the ISNA function to detect whether a VLOOKUP function returns an NA error or not, while IF function is responsible for determining the result. If no NA error occurs, the result is from the VLOOKUP function, if an NA error occurs the result is the number 0.
The Formula
=IF(ISNA(VLOOKUP(F4,$A$2:$D$13,2,FALSE)),0,VLOOKUP(F4,$A$2:$D$13,2,FALSE))
The Result
The results remain the same as the two previous solutions.
Which One is the Best Solution
The IFERROR function and IFNA function are available in Excel 2013 or the newer version. If both functions are available in your excel version, please choose one. Solution #1 and solution #2 are the best solutions; both produce a more straightforward formula.
If the IFERROR function or IFNA function is not available, then solution #3 is the only choice you have. The formula is indeed longer because you must write the VLOOKUP function twice, but it solves the problem.
Another Alternative
The three solutions above all prevent NA errors to occurs to calculate the total bill. There is another solution by allowing NA errors to occurs, and excel can calculate the entire bill.
Please read the article below for a detailed explanation. a different case example, but you can solve the VLOOKUP NA error with the same function
