#N/A

[JJ in SD]

Good Morning,

How do I loose the #N/A from this =VLOOKUP(A4,$A$13:$H176,7,TRUE) if the lookup cells are empty?

Thanks in advance,

JJ in SD

 

[Joe4]

One way (though not the most efficient), is to use the ISNA function:

=ISNA(VLOOKUP(A4,$A$13:$H176,7,TRUE),"",VLOOKUP(A4,$A$13:$H176,7,TRUE))

 

[lenze]

A slight edit to Jimiskey's post

=IF(ISNA(VLOOKUP(A4,$A$13:$H176,7,TRUE),"",VLOOKUP(A4,$A$13:$H176,7,TRUE)))

 

[Mark W.]

=IF(ISBLANK(A4),"",VLOOKUP(A4,$A$13:$H176,7,TRUE))

 

[JJ in SD]

Thanks Guys

 

[Mark W.]

Of course, you could also insert a new 1st record to your lookup_table with 0 in column A and ="" in column 7.

PS: Also, make sure that your lookup_table is sorted in ascending order based on the values in column A.

 

[Joe4]

Thanks for the pick-up Lenze! My mind is in Monday mode...

 

[Aladin Akyurek]

Good Morning,

How do I loose the #N/A from this =VLOOKUP(A4,$A$13:$H176,7,TRUE) if the lookup cells are empty?

Thanks in advance,

JJ in SD

If you have the morefunc add-in, you can have...

=IF(A4<>"",IF(ISNA(SETV(VLOOKUP(A4,$A$13:$H176,7))),"",GETV()),"")

 

[Mark W.]

JJ in SD, you won't need the morefunc add-in since your range_lookup value is TRUE. A #N/A can only occur if your lookup_value is less than the 1st value in column A.

=IF(ISBLANK(A4),"",VLOOKUP(A4,$A$13:$H176,7,TRUE))

...will suffice. However, you could get by with just...

=VLOOKUP(A4,$A$13:$H176,7,TRUE)

...if you're willing to insert a new 1st record (with a 0 in column A) in your table array (as I mentioned earlier).

 

[Aladin Akyurek]

Good Morning,

How do I loose the #N/A from this =VLOOKUP(A4,$A$13:$H176,7,TRUE) if the lookup cells are empty?

Thanks in advance,

JJ in SD

If you have the morefunc add-in, you can have...

=IF(A4<>"",IF(ISNA(SETV(VLOOKUP(A4,$A$13:$H176,7))),"",GETV()),"")

Forget this contrib if A13:A176 is numeric and sorted in ascending order (as it has been noted). In case you need to switch to an exact match, the formula would hold.