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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

masim

Board Regular
Joined
Jun 19, 2003
Messages
130
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
 

rwilding

Board Regular
Joined
Jul 17, 2003
Messages
103
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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)
 

dontgetit

Board Regular
Joined
Mar 31, 2003
Messages
94

ADVERTISEMENT

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

exceluser55

New Member
Joined
Sep 19, 2009
Messages
13

ADVERTISEMENT

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
 

c_m_s_jr

Well-known Member
Joined
Mar 23, 2009
Messages
1,561
Wayneooo maybe if you gave some sample data and explained what your trying to do with your formula a solution can be found.
 

exceluser55

New Member
Joined
Sep 19, 2009
Messages
13
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:

Watch MrExcel Video

Forum statistics

Threads
1,130,295
Messages
5,641,401
Members
417,208
Latest member
wendy823

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
Top