How do I write a VLOOKUP function that returns 0's, not neg vals?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dbsavoy - 24 Aug 2006 00:24 GMT

I need to write a VLOOKUP function that will return a value of "0" if it
finds the number for which it had searched is negative. My thought is to use
an IF logic function together with the VLOOKUP, but I just can't seem to
successfully weave the two together.

Any help would be much appreciated. Thanks!

Reply to this Message

Bob Phillips - 24 Aug 2006 00:32 GMT

=MAX(0,vlookup_formula)

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> I need to write a VLOOKUP function that will return a value of "0" if it
> finds the number for which it had searched is negative. My thought is to use
> an IF logic function together with the VLOOKUP, but I just can't seem to
> successfully weave the two together.
>
> Any help would be much appreciated. Thanks!

Reply to this Message

dbsavoy - 24 Aug 2006 16:25 GMT

Thanks Bob, this seems to work standalone, but I still can't figure out how
to integrate it with the formula I'm using:
=IF(ISNA(VLOOKUP($A10,Pg2!$A$2:$M$453,Y$3,FALSE)),"",VLOOKUP($A10,'NA
Pg2'!$A$2:$M$453,Y$3,FALSE))

I must be a bit slow on the uptake. Where in this formula can I specify
that neg. values should be returned as Zero?

> =MAX(0,vlookup_formula)
>
[quoted text clipped - 5 lines]
> >
> > Any help would be much appreciated. Thanks!

Reply to this Message

Bob Phillips - 24 Aug 2006 16:31 GMT

I think this is what you need

=IF(ISNA(VLOOKUP($A10,Pg2!$A$2:$M$453,Y$3,FALSE)),"",
MAX(0,VLOOKUP($A10,'NA Pg2'!$A$2:$M$453,Y$3,FALSE)))

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Thanks Bob, this seems to work standalone, but I still can't figure out how
> to integrate it with the formula I'm using:
[quoted text clipped - 13 lines]
> > >
> > > Any help would be much appreciated. Thanks!

Reply to this Message

dbsavoy - 24 Aug 2006 17:26 GMT

That did it! Bob, you've made my day. :-)

Thx!

> I think this is what you need
>
[quoted text clipped - 22 lines]
> > > >
> > > > Any help would be much appreciated. Thanks!

Reply to this Message