How to Handle Excel VLOOKUP Errors

In the prior Vlookup post we reviewed how to create a basic Vlookup formula; combining employee hours with employee addresses so that we can pay employees for the number of hours billed. The formula however was not perfect as a few cells resulted in an #N/A error. Let’s continue to develop this formula and go over why we encountered this error, other errors we may encounter, and how to handle them.

End file from prior post:
Excel_VlookupEnd
vlookupfeatured


#N/A

#N/A is the error excel provides when a formula result cannot be found. In the attached example we see this error for Bookerson,Greg. Comparing the Hours Billed and Address data sets, we can see that his employee ID, 22222 was not provided in the Address data set.

We could leave the formula as is, but lets change the error to a Blank "" cell to make things look a bit cleaner.

There are two primary formulas that can help us do this:
IFERROR() 
and
IF(ISNA())


IFERROR()

The easiest of the two formulas, IFERROR()) allows us to replace the #N/A error with a value of our choosing.

For text type fields I choose “” for blank/null
For number type fields I choose 0 so that I can sum them easily later on

  1. Click in the Vlookup formula in Fx
  2. After the equal sign type IFERROR(
  3. You will see excel will prompt you with Value, Value If Error.  The Vlookup formula result is going to be our Value
  4. Place your cursor to the far right of the formula
  5. Press comma
  6. Type "" and close your parenthesis
  7. Press ENTER
    Your formula result should look like:
    =IFERROR(VLOOKUP(A2,J:M,4,0),"")
  8. Fill the formula down to remove the errors
    Vlookup4

ISNA()

This function is similar to IFERROR() in that it looks for #N/A errors, except that it only returns TRUE and FALSE if it finds one. For example, if I place the formula in G2 and have it reference F2, I will get the below:
=ISNA(F2)
ISNA.jpg

Let’s insert the ISNA() function into our Vlookup in column F.
In order to do this, we will need to create an IF-Then-Else statement for our Vlookup. More on IF statements later on.

The formula we will use is:
=IF(ISNA(VLOOKUP(A2,J:M,4,0)),"",(VLOOKUP(A2,J:M,4,0)))

Let’s break this formula down…
Logically, we are saying:
IF the Vlookup result is an error, then show it as blank.
IF the Vlookup is not an error, then show the Vlookup result.

isna1


Unique Identifiers

It is important to note that lookups should be done using unique identifiers. A Unique Identifier is something that identifies an object or entity and is unique to that object or entity. Think, social security number.

For the above lookup examples we used the Employee ID as our Unique Identifier as no employee ever has the same Employee ID.
Let’s show a Vlookup error that could result from not using employee ID.Vlookup5

In the above example, I’ve changed our:
Lookup_Value from A2 to B2
Table_Array from J:M to K:M
Col_index_num from 4 to 3
Range_Lookup stays at 0

Note how Row 7, Page, Holly has a Vlookup error now. This is due to Holly’s name having a space between the Comma and her first name whereas her other records in hours billed and addresses do not have that space.

Different systems you work in may log names differently. Some may be
“Last,First”
“Last, First”
while others may include employee preferred names where
“Chan, Pang Lee” would show as “Chan, Steve”

Always, Always, Always, use a unique identifier when available. If it is not available, request it from the person who sent you the data and/or IT.



Categories: Excel, Vlookups

Tags: , , , , , , ,

Leave a Reply

Show Buttons
Hide Buttons
%d bloggers like this: