Fixing Excel Vlookup Error - #n/a

Fixing excel VLOOKUP error - #N/A

Knowing error:

In Vlookup formulas, the #N/A error message (meaning "not available") is displayed when Excel cannot find a lookup value. There can be several reasons why that may happen.

If you are using a formula with approximate match (range_lookup argument set to TRUE or omitted), your Vlookup formula might return the #N/A error in two cases:

1. If the lookup value is smaller than the smallest value in the lookup array.
2. If the lookup column is not sorted in ascending order.

If you are searching with exact match (range_lookup argument set to FALSE) and the exact value is not found, the #N/A error is also returned. 

As you probably know, one of the most significant limitations of Excel VLOOKUP is that it cannot look to its left, consequently your lookup column should always be the left-most column in the table array. In practice, we often forget about this fact and end up with VLOOKUP not working because of the N/A error.

Another source N/A errors in VLOOKUP formulas is numbers being formatted as text, either in the main or lookup table.

This usually occurs when you import data from some external database or if you've typed an apostrophe before a number to indicate a leading zero.

The numbers can also be stored in the General format. In this case, there's only one noticeable sign - numbers get aligned to the left side of a cell, while numbers stored as numbers are aligned to the right by default.

Solution:

1-Using If & ISNA formula combination we can suppress #N/A error.

First understand what is use of ISNA formula.

ISNA checks whether result of given formula is #N/A that is an excel way to tell us that required result is not available. If formula result is #N/A then ISNA formula return TRUE else FALSE as a output.

So formula will be: =ISNA(VLOOKUP(F4,A1:D9,2,0))

See cell J5 for information.

Using If & ISNA together.

We will test if isna is true then give result “ID is Missing” else result actual value.

So formula will be: =IF(J5,"ID Missing",VLOOKUP(F4,A1:D9,2,0))

See cell J6 for information.

Final Formula

So our final formula will look like this:

=IF(ISNA(VLOOKUP(F4,A1:D9,2,0)),"ID Missing",VLOOKUP(F4,A1:D9,2,0))

Refer Attachment fpr Practice.