Rid the N/A# in vlookup

dontgetit

Board Regular
Joined
Mar 31, 2003
Messages
94
Good day

Can someone please tell me what I have to add to a Vlookup formula to have it bring back a 0 or blank cell instead of N/A# when there is no result.

Please help !

Thanks
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
Thanks everyone for your reponses. I was able to use a couple of thses formulas to get the results I needed.
:biggrin:
 
Upvote 0
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
 
Upvote 0
Wayneooo maybe if you gave some sample data and explained what your trying to do with your formula a solution can be found.
 
Upvote 0
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:

Excel Workbook
ABCDE
1601/01/200001/06/2000Is there evidence on TDR system?
2820/01/200318/09/2003Does customer provide details?
3701/03/200001/06/2001Is there evidence in on TDR system?Is there evidence in on MD5?
4706/01/200509/02/2009Is evidence consistent with LPR and TSI?
Stuff


FROM_DATES:

Excel Workbook
B
101/01/2000
220/01/2003
301/03/2000
406/01/2005
Stuff


TO_DATES:

Excel Workbook
C
101/06/2000
218/09/2003
301/06/2001
409/02/2009
Stuff






Excel Workbook
ABCDE
1201/01/200001/06/2000Is there evidence on TDR system?
2420/01/200318/09/2003Does the request conflict on notes?
3701/01/200503/12/2005Is there evidence in on TDR system?Is there evidence in on MD5?
4706/01/200509/02/2009Is evidence consistent with LPR and TSI?
Stuff
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top