# Rid the N/A# in vlookup

#### dontgetit

##### Board Regular
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.

Thanks

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### masim

##### Board Regular
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
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

##### MrExcel MVP
A couple of options in order of efficiency and beauty...

In B1 enter:

=VLOOKUP(LookupValue,LookupTable,ColIdx,0)

In A1 enter:

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

=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

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

#### joep

##### New Member
Many thanks the ISNA function works wonderfully!

#### exceluser55

##### New Member

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
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
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:

Replies
1
Views
66
Replies
1
Views
90
Replies
5
Views
56
Replies
0
Views
125
Replies
7
Views
79

### Forum statistics

1,130,213
Messages
5,640,909
Members
417,178
Latest member
HelpMeExcelExperts

### 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.

### Which adblocker are you using?

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

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