Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

Handling an #N/A Error When Using VLOOKUP

VLOOKUP is one of the lookup and reference functions in Excel that is used to find values in a specified range by “row.” It compares them row-wise until it finds a match. In this tutorial, we will look at some instances where the VLOOKUP function shows a #N/A error and a few workarounds to fix it.

VLOOKUP and Handling an #N/A Error

The syntax for VLOOKUP is.=VLOOKUP (value, table_array, col_index, [range_lookup]) It returns a #N/A error if it cannot find a match to the lookup value.

1. No match is found:

  • If range_lookup is set to FALSE and the lookup value does not exist in your table_array, VLOOKUP returns a #N/A. We will use the employee database to demonstrate this. If you wanted to find out the department for ID “4600” using VLOOKUP in cell C9.

    It would return a #N/A as “4600” does not exactly match with any of the entry Employee ID in column A.

  • If range_lookup is set to TRUE and the lookup value is smaller than the smallest value in the table_array, VLOOKUP will return a #N/A error.

    In this example, no values in the range B2:E6 range that is lesser than the lookup_value 1500.

  • If range_lookup is set to TRUE and any of the lookup columns are not sorted in ascending order, a #N/A error is returned. In the following example, the range_lookup is set to TRUE, and the lookup column is not sorted in ascending order resulting cell C9 in a #N/A error.

2. Multiple Matches found:

For multiple matches, VLOOKUP pulls over the first match in the table.

Here, the lookup column has multiple matches for the lookup_value. VLOOKUP returns the first match. If we wanted the second match, this wouldn’t be much help.

You can avoid #N/A errors by taking a few precautions. But often, the data is not fully clean and tidy. At these times you need to handle these errors with custom text. In the next section, we will see some functions to handle these errors.

ISERROR() function:

You can use ISERROR() along with IF() to handle VLOOKUP returning #N/A errors. To prevent #N/A error in cell B9,

  1. Click cell B9.
  2. Click on the formula bar and assign the formula
    “=IF(ISERROR(VLOOKUP(B9,$B$2:$E$6,3,FALSE)),"Employee not found",VLOOKUP(B9,$B$2:$E$6,3,TRUE))”

  3. Press Enter.

Cell B9 has the value “3568” which is not present in the table_array. This would normally result in a #N/A error. However, the ISERROR() formula nested within the IF() formula handles the #N/A error and outputs our custom message “Employee not found.”

ISNUMBER() function:

The ISNUMBER() function will check if a value is a number. ISNUMBER() will return TRUE when the value is numeric and FALSE when not.

In the following example, you will sum up a grocery bill. The data is on cells B2:B6. On cells B9:B11 we have the purchased items list. The corresponding cells C9, C10, and C11 use the formula to“=VLOOKUP(B9,$B$2:$D$6,3,FALSE)” extract the price. However, cell B10 has product ID 3dfdw which doesn’t have a match in the table_array. As a reason, the corresponding cell C10 is a #N/A. Cell C12 needs to have the total. If you tried to sum C9:C11 you will get a #N/A error as C10 is #N/A.

In order to handle the #N/A error of the prices, you need to:

  1. Click on cell C12.
  2. On cell C12, assign the formula
    ” =SUM(IF(ISNUMBER(CHOOSE({1,2,3},C9,C10,C11)),CHOOSE({1,2,3},C9,C10,C11)))”

  3. Press Ctrl + Shift + Enter to apply this formula to the cell as it is an array formula. This formula uses the ISNUMBER() and CHOOSE() function nested inside a SUM function. The CHOOSE() function chooses a value from the index which is checked by ISNUMBER() to be a value. Then the resulting numbers are summed and are displayed in cell C12.

VLOOKUP is one of the most effective functions in Excel for lookup and reference. But you need to be careful using it to prevent #N/A errors. Most of these errors can be avoided to some extent. But some errors would remain even after taking all possible measures. For these cases, the existing errors need to be handled. ISERROR() and ISNUMBER() are two such functions used to handle #N/A errors associated with VLOOKUP().

There are many cases in which VLOOKUP may result in errors. Sometimes, the problem is your data set-up. If you want to save hours of researching and frustration, try our Excel Live Chat service! Our Excel experts are available 24/7 to answer any Excel question you have on the spot. The first question is free.

Are you still looking for help with the VLOOKUP function? View our comprehensive round-up of VLOOKUP function tutorials here.

 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Solution examples
I want to display a range of cells if they meet a specific criteria. I want to display a name IF it does not equal one of three options AND there are any numbers contained in a range of cells
Solved by G. F. in 60 mins
I need to find out if the 2018 yearly sales goals were met if the yearly sales were $25,000 or more using an IF logical function and to set the formula to return a value of YES if met, and NO if not
Solved by A. A. in 18 mins
I filtered data in column A (it is labeled ID #) of a data set. On my next worksheet, column A is also the ID #, but it is a different data set. I want to filter the ID #'s the same for the two sheets, but how do I do that being that I am working with two different sets of data?
Solved by V. H. in 22 mins
I'm looking for a formula or format for if there is data in c4 on a sheet... It adds extra information underneath
Solved by X. E. in 20 mins
ï?·In cell G1enter Discount, ï?·In cell H1enter DiscountPercent,ï?·In cell I1enter DiscountCost,ï?·In cell J1enter TotalCost, andï?·In cell K1enter Paymentas column headings.
Solved by K. E. in 40 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc