Function problem

[andonny]

Hi,
The function below does not return what I need and I can't work it out.

,=IF(ISNA(-VLOOKUP(E21,Stocktake!$A$1:$I$65000,9,FALSE)),-T213,-VLOOKUP(E21,Stocktake!$A$1:$I$65000,9,FALSE)-T21)

When E21 is blank or it can't fine any data in Stocktake!$A$1:$I$65000 it needs to return the a negative value of T213. If I have 5 in T213 then it should return -5.

Your help is very much appreciated
Andonny

 

[Nimrod]

What does it return in those cases ?

 

[Aladin Akyurek]

On 2002-05-11 01:03, andonny wrote:
Hi,
The function below does not return what I need and I can't work it out.

,=IF(ISNA(-VLOOKUP(E21,Stocktake!$A$1:$I$65000,9,FALSE)),-T213,-VLOOKUP(E21,Stocktake!$A$1:$I$65000,9,FALSE)-T21)

When E21 is blank or it can't fine any data in Stocktake!$A$1:$I$65000 it needs to return the a negative value of T213. If I have 5 in T213 then it should return -5.

Your help is very much appreciated
Andonny

=IF(COUNTIF(Stocktake!$A$1:$A$65000,E21),-VLOOKUP(E21,Stocktake!$A$1:$I$65000,9,0)-T21,-T213)

Caveat. If you want to have a negative result even if T213 already has a negative value, then you could also use:

=-IF(COUNTIF(Stocktake!$A$1:$A$65000,E21),VLOOKUP(E21,Stocktake!$A$1:$I$65000,9,0)-T21,ABS(T213))

Note. I used 0 instead of FALSE (they mean same thing) in VLOOKUP.

Aladin

 

[Nimrod]

=IF(ISNA(-VLOOKUP(E21,StockTake!$A:$I000,9,FALSE)),-INDEX(T:T,213,T:T),-VLOOKUP(E21,StockTake!$A:$I000,9,FALSE)-INDEX(T:T,21,T:T))

This message was edited by Nimrod on 2002-05-11 01:42

 

[Nimrod]

Very Nice Code Aladin