Thanks:  0
Likes:  0

1. 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

2. What does it return in those cases ?

3. 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.

4. Code:
`=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 ]

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•