Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Function problem

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    220
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What does it return in those cases ?

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,034
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Very Nice Code Aladin

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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