Vlookup

[Juran]

I have a Vlookup formula which gives me #N/A when the data
is not found.

Is there a way the return is "0" or blank instead of #N/A?

Thank you.

Juran

 

[Debra Dalgleish]

You can us an IF formula with the VLookup, e.g.:

=IF(ISNA(VLOOKUP(A8,$J$1:$J$10,2,FALSE)),"",VLOOKUP(A8,$J$1:$J$10,2,FALSE))

 

[Juran]

Debra

Thank you.

Juran

-----Original Message-----
You can us an IF formula with the VLookup, e.g.:

=IF(ISNA(VLOOKUP(A8,$J$1:$J$10,2,FALSE)),"",VLOOKUP(A8,$J$1:$J$10,2,FALSE))





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.

 

[Andy Wiggins]

Similar to Debras formula:
=IF(COUNTIF(A1:A10,E1)>0,VLOOKUP(E1,A1:C10,2,FALSE),"")
Assumes the value you are looking up is in cell E1.
This construction is less expensive in processor time.

--
Regards
-
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy

 

[RagDyeR]

Talking about efficiency, I believe this might be even more so then
Countif():

=IF(ISNA(MATCH(E1,A1:A10,0)),"",VLOOKUP(E1,A1:C10,2,0))

Sort of remember some speed tests posted a couple of years ago comparing
various error trapping methods.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


"Andy Wiggins" <newsgroups at bygsoftware d o t c o m> wrote in message
Similar to Debras formula:
=IF(COUNTIF(A1:A10,E1)>0,VLOOKUP(E1,A1:C10,2,FALSE),"")
Assumes the value you are looking up is in cell E1.
This construction is less expensive in processor time.

--
Regards
-
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy