Dealing with Errors in Lookups

The #N/A Error

If we use the VLOOKUP Function to match between two sets of data – then occasionally, Excel will return an #N/A error where it can’t find the value in one list in the other one

In this example, we have two lists of employee data where in the “Month #2” block, there’s a calculation to see if the current employees are the same as last month (simply by checking whether their Employee ID is the same. If it is, in other words the employee was also employed last month, then it brings their Employee ID over; if not, then it shows a #N/A error as it’s unable to find the required value (in this case 1243 or 1244) in the ID column for Month #1

If this was just for “internal” use – for example, it was a quick check that I was doing to see who the new people were (or perhaps I was using it to compare details on a bank or supplier’s reconciliation), then I’d probably leave it like this… I know why the error has occurred & all I need is to know which the relevant entries are.

However, if this is for “external” consumption, or, perhaps, for someone who needs to know what the #N/A error relates to, then we need to do something further. The “old way” was to use an IF Function with an ISERROR one to generate something like this…

=IF(ISERROR(VLOOKUP(F4,$A$4:$C$12,1,FALSE)),”New Employee”,VLOOKUP(F4,$A$4:$C$12,1,FALSE))

This checks if the VLOOKUP generates an error & if it does, it returns the text string “New Employee”, if it doesn’t, it returns the value that is the employee ID

However, to say the least, that’s a little “clunky” (and easy to mistype!), which is why the IFERROR Function was created

The IFERROR() Function

The IFERROR() Function does the above in a far simpler way, the syntax is…

IFERROR(value, value_if_error)

Or, in English: If the calculation works, then show its result; if it doesn’t then show this value

So, our error handling calculation above would simply become…

=IFERROR(VLOOKUP(F4,$A$4:$C$12,1,FALSE),”New Employee”)

A quick note on Text Strings

In each of the error handling methods above, if we wanted the phrase New Employee to appear, we need to enclose it in double quote marks (SHIFT+2) to signify that it is a text string. If you don’t do this, you’ll risk creating a different error: #Name? where Excel thinks that New Employee is the name of a range that you’ve set & it generates the error because it can’t find it.

We’ll discuss different error types next month