Rid the N/A# in vlookup

Manipulate the vlookup part of the following formula to adjust to your requirements

=IF(ISNA(VLOOKUP(A6,$A$1:$B$2,2,0)),0,(VLOOKUP(A6,$A$1:$B$2,2,0)))


I hope this would help

Regards
Asim

What I have done is put in a simple IF statement, so that if B8 (the cell that has the data you are comparing with) is blank the VLOOKUP cell will be blank, otherwise perform the VLOOKUP.

=IF(B8="","",VLOOKUP(B8,$K$2:$L$3130,2,0))


Hope this helps,
Richard

A couple of options in order of efficiency and beauty...

1] Use an additional cell:

In B1 enter:

=VLOOKUP(LookupValue,LookupTable,ColIdx,0)

In A1 enter:

=IF(ISNA(B1),0,B1)

2] Download & install the free morefunc.xll add-in & use:

=IF(ISNA(SETV(VLOOKUP(LookupValue,LookupTable,ColIdx,0))),0,GETV())

which is as efficient as Option 1.

3] If Option 2 is not feasible, add the following code to your workbook as a module:

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

and use:

=IF(ISNA(V(VLOOKUP(LookupValue,LookupTable,ColIdx,0))),0,V())

4] If efficiency is not important to you, use either

=IF(ISNUMBER(MATCH(LookupValue,INDEX(LookupTable,0,1),0)),VLOOKUP(LookupValue,LookupTable,ColIdx,0),0)

or

=IF(ISNA(VLOOKUP(LookupValue,LookupTable,ColIdx,0)),0,VLOOKUP(LookupValue,LookupTable,ColIdx,0))

5] Reject using either senseless

=IF(ISERROR(VLOOKUP(LookupValue,LookupTable,ColIdx,0)),0,VLOOKUP(LookupValue,LookupTable,ColIdx,0))

or inefficient

=IF(COUNTIF(LookupValue,INDEX(LookupTable,0,1)),VLOOKUP(LookupValue,LookupTable,ColIdx,0),0)

Thanks everyone for your reponses. I was able to use a couple of thses formulas to get the results I needed.

Many thanks the ISNA function works wonderfully!

Hi there.

I have a similar problem, though my VLOOKUP formula is more complex, i've been unable to surpress the #VALUE! error using your suggestions. Can anybody advise?

My formula:

=VLOOKUP($A2,IF($B2>=(FROM_DATES),IF(Sheet1!$B2<=(TO_DATES),FULL_TABLE,""),""),5,FALSE)

The lookup works fine. However if no match, it returns #VALUE!. I want it to simply stay blank.

Any help appreciated.

Thanks

Wayneooo maybe if you gave some sample data and explained what your trying to do with your formula a solution can be found.

After entering the date and selecting a complaint from dropdown box in cell C, the lookup formulas in cells D and F check a table on 'Stuff' sheet which contains upto two questions asked for specific complaints and date ranges. If a match is found, the questions are returned to Sheet1 cells D and F respectively.

There can be multiple date ranges for a single complaint point. Sometimes one question is asked, sometimes two. Only one date range will apply though.

The lookup function as it stands works fine, i just want to surpress #VALUE! when no match is found, and also perhaps surpress the 0 when cell E on sheet Stuff is empty (this implies only one question is asked - the formula in Sheet1 cell D will return the first question, but as no second question, the formula in Sheet1 cell E will return '0').

The sample data here contains examples of three outcomes:

a) (ROW 1) - Lookup successful. One matching question found. No second question, so '0' is returned. I want it to be blank instead of 0.

b) (ROW 2) - Lookup unsuccessful. No matches found. Both cells return #VALUE!. I want it to be blank instead of #VALUE!.

c) (ROW 3) - Lookup successful. Two matching questions found.



NAMED RANGES

FULL_TABLE:



FROM_DATES:



TO_DATES: