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:
#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:
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
- Click in the Vlookup formula in
- After the equal sign type
- You will see excel will prompt you with
Value, Value If Error. The Vlookup formula result is going to be our Value
- Place your cursor to the far right of the formula
- Press comma ,
""and close your parenthesis
Your formula result should look like:
- Fill the formula down to remove the errors
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:
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:
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.
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.
In the above example, I’ve changed our:
Range_Lookup stays at
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
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.