VLOOKUP-returns "0" if number are less than zero

Posted by Squirrel on December 07, 2001 8:29 PM

Hello O great ones" First I would like to say that I visit this board on a daily basis for over a year and I owe alot to the people that post here including the Questions. Thanks to everyone. I have a spreedsheet that Imports a report using monarch from a AS400 system, Than I use VLOOKUP to fill the cells with the content needed. What I would like is to have the cell return zero where the number is less than zero. Example -5 (minus 5) may be returned

Posted by Bariloche on December 07, 2001 10:09 PM

Squirrel,

If I understand your need correctly, just use your Vlookup in an "If" function, like so:

=IF(VLOOKUP(F2,A$1:B$16,2,FALSE)&LT;0,0,VLOOKUP(F2,A$1:B$16,2,FALSE))

In other words, if vlookup is less than zero, put in zero, otherwise use the vlookup value.

have fun

Posted by IML on December 08, 2001 9:44 AM

Also

Also, you could use
=max(0,vlookup(a1,B1:B10,2,0))