How to get VLOOKUP to return zero is not found???

**Tanasi** · 03-25-2009, 03:00 PM

I'm using VLOOKUP to search for results in another worksheet. What I need is 0 returned if the search fails. Currently I'm getting #N/A and that's causing my other formulas to fail.
Any suggestions???
TIA

**DonkeyOte** · 03-25-2009, 03:06 PM

A couple...

=IF(ISNA(VLOOKUP(....)),0,VLOOKUP(....))

or assuming results of VLOOKUP when found are numeric then another alternative:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,VLOOKUP(...)))

**Tanasi** · 03-25-2009, 03:13 PM

Originally Posted by DonkeyOte

A couple...

=IF(ISNA(VLOOKUP(....)),0,VLOOKUP(....))

or assuming results of VLOOKUP when found are numeric then another alternative:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,VLOOKUP(...)))

I don't even understand your answer, I guess my vast lack of knowledge is more than showing.
My current formula is:
=VLOOKUP(C1,'IMPORT-A'!A:H,3,0)
If not found I would like the result to be 0(zero).

Please forgive my ignorance.

**DonkeyOte** · 03-25-2009, 03:18 PM

In the first suggestion simply replace the references to VLOOKUP(...) with your own VLOOKUP, ie:

=IF(ISNA(VLOOKUP(C1,'Import-A'!A:H,3,0)),0,VLOOKUP(C1,'Import-A'!A:H,3,0))

Note: given you're pulling Column C you need not reference A:H you could just reference A:C thereby reducing dependencies... you could use INDEX/MATCH to reduce further but that's for another day.

**Tanasi** · 03-25-2009, 04:07 PM

Originally Posted by DonkeyOte

In the first suggestion simply replace the references to VLOOKUP(...) with your own VLOOKUP, ie:

=IF(ISNA(VLOOKUP(C1,'Import-A'!A:H,3,0)),0,VLOOKUP(C1,'Import-A'!A:H,3,0))

Note: given you're pulling Column C you need not reference A:H you could just reference A:C thereby reducing dependencies... you could use INDEX/MATCH to reduce further but that's for another day.

My formula:
=IF(ISNA(VLOOKUP(A2,'Import-A'!A:C,3,0)),0,(VLOOKUP(A2,'Import-A'!A:C,3,0)))
Returns FALSE, how do I get the numeric value at that location to come over if false???