Excel - Return Zero if vlookup is not true

[dsmith910]

Hi

I have a spreadsheet one column of which has a vlookup function in it

=VLOOKUP(A1,Sheet1!A1:B12,2,1)

This works fine but if the condition is not met it puts the last value in the array in. Can I add something which says if A1 doesn't contain one of the numbers on the vlookup array sheet return a value of, say, zero?

Doreen

 

[xlbo]

It's returning the neare4st m,atch because you have set the 4th argument to 1 (TRUE). If you set this to false, it will look for an EXACT match only eg:
=VLOOKUP(A1,Sheet1!A1:B12,2,FALSE)

to return a zero on a non match, just use:
=if(isna(VLOOKUP(A1,Sheet1!A1:B12,2,false)),0,VLOOKUP(A1,Sheet1!A1:B12,2,false))

ALSO - you should probably use ABSOLUTE referencing for your range eg $A$1:$B$12

Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[onedtent]

=if(iserror(VLOOKUP(A1,Sheet1!A1:B12,2,False),"",VLOOKUP(A1,Sheet1!A1:B12,2,False))

will give you a blank cell, or if u like u can change "" to zero

 

[dsmith910]

Hi

Many thanks! did a temporary fix by putting a 'last line' in which 'invalid'. However this would be a pain if I had to always do it as the lookup array is actually a list copied from a query in Access.

Am dashing off to try out your solutions.

Thanks again

Doreen

 

[xlbo]

oneddent - just as a precautionary note - ISERROR will do that for ANY error - not just a "not found" error. Vlookups can also return REF! errors (when you have a "look across" greater than hte range ref) so I prefer to use ISNA which will specifically capture "Not Founds" Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[dsmith910]

Hi Geoff

Will bear this in mind. Thanks for the tip.

Doreen