Rid the N/A# in vlookup

[dontgetit]

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

[masim]

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]

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]

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]

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

[joep]

Many thanks the ISNA function works wonderfully!

[wayneooo]

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]

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

[wayneooo]

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.

Sheet1

	A	B	C	D	E	F	G
1	01/05/2000	6	Optional	Is there evidence on TDR system?		0
2	01/05/2000	8	Nothing explained	#VALUE!		#VALUE!
3	01/05/2000	7	Costs	Is there evidence in on TDR system?	No	Is there evidence in on MD5?	No

Spreadsheet Formulas

Cell Formula B1 =VLOOKUP($C1,complaints_full, 2, FALSE) D1 =VLOOKUP($B1,IF($A1>=(FROM_DATES),IF(Sheet1!$A1<=(TO_DATES),FULL_TABLE,""),""),4,FALSE) F1 =VLOOKUP($B1,IF($A1>=(FROM_DATES),IF(Sheet1!$A1<=(TO_DATES),FULL_TABLE,""),""),5,FALSE) A2 =IF(A1="","",A1) B2 =VLOOKUP($C2,complaints_full, 2, FALSE) D2 =VLOOKUP($B2,IF($A2>=(FROM_DATES),IF(Sheet1!$A2<=(TO_DATES),FULL_TABLE,""),""),4,FALSE) F2 =VLOOKUP($B2,IF($A2>=(FROM_DATES),IF(Sheet1!$A2<=(TO_DATES),FULL_TABLE,""),""),5,FALSE) A3 =IF(A2="","",A2) B3 =VLOOKUP($C3,complaints_full, 2, FALSE) D3 =VLOOKUP($B3,IF($A3>=(FROM_DATES),IF(Sheet1!$A3<=(TO_DATES),FULL_TABLE,""),""),4,FALSE) F3 =VLOOKUP($B3,IF($A3>=(FROM_DATES),IF(Sheet1!$A3<=(TO_DATES),FULL_TABLE,""),""),5,FALSE)

Excel tables to the web >> Excel Jeanie HTML 4

NAMED RANGES

FULL_TABLE:

Stuff

	A	B	C	D	E
1	6	01/01/2000	01/06/2000	Is there evidence on TDR system?
2	8	20/01/2003	18/09/2003	Does customer provide details?
3	7	01/03/2000	01/06/2001	Is there evidence in on TDR system?	Is there evidence in on MD5?
4	7	06/01/2005	09/02/2009	Is evidence consistent with LPR and TSI?

Excel tables to the web >> Excel Jeanie HTML 4

FROM_DATES:

Stuff

	B
1	01/01/2000
2	20/01/2003
3	01/03/2000
4	06/01/2005

Excel tables to the web >> Excel Jeanie HTML 4

TO_DATES:

Stuff

	C
1	01/06/2000
2	18/09/2003
3	01/06/2001
4	09/02/2009

Excel tables to the web >> Excel Jeanie HTML 4





Stuff

	A	B	C	D	E
1	2	01/01/2000	01/06/2000	Is there evidence on TDR system?
2	4	20/01/2003	18/09/2003	Does the request conflict on notes?
3	7	01/01/2005	03/12/2005	Is there evidence in on TDR system?	Is there evidence in on MD5?
4	7	06/01/2005	09/02/2009	Is evidence consistent with LPR and TSI?

Excel tables to the web >> Excel Jeanie HTML 4