vlookup
Posted by Andonny on October 18, 2001 6:33 PM
Hi,
I have this formula =VLOOKUP(E12,Rev!$B$9:$G$48,6,FALSE) in column B and C.
In column B it should only place the found figure if it is positive otherwise it should be blank. I column C it should only place the found figure if it is negative otherwise it should be blank.
Thank you for your kind help
Andonny
Posted by Richard S on October 18, 2001 7:19 PM
:Andonny
=if(VLOOKUP(E12,Rev!$B$9:$G$48,6,FALSE)>0,=VLOOKUP(E12,Rev!$B$9:$G$48,6,FALSE),"") in column B
=if(VLOOKUP(E12,Rev!$B$9:$G$48,6,FALSE)<0,=VLOOKUP(E12,Rev!$B$9:$G$48,6,FALSE),"") in column C
Richard
Posted by lenze on October 19, 2001 5:50 AM
Posted by IML on October 19, 2001 6:15 AM
If you can get away with zeros instead of blanks, you could avoid the double lookup with
=max(VLOOKUP(E12,Rev!$B$9:$G$48,6,FALSE),0) for positives and
=min(VLOOKUP(E12,Rev!$B$9:$G$48,6,FALSE),0) for negatives.
