Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Help with Formula (e)

    Hi

    I need some help understanding this formula I would be grateful for some help.

    =IF(ISERROR(IF((VLOOKUP('Enter formula'!$H7,'Raw materials'!$A$2:$CQ$1799,95,FALSE)="colorant"),VLO OKUP('Enter formula'!$H7,'Raw materials'!$A$2:$CQ$1799,79),"-")),"",IF((VLOOKUP('Enter formula'!$H7,'Raw materials'!$A$2:$CQ$1799,95,FALSE)="colorant"),VLO OKUP('Enter formula'!$H7,'Raw materials'!$A$2:$CQ$1799,79),"-"))

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with Formula (e)

    Let's take the expression

    IF((VLOOKUP('Enter formula'!$H7,'Raw materials'!$A$2:$CQ$1799,95,FALSE)="colorant"),VLO OKUP('Enter formula'!$H7,'Raw materials'!$A$2:$CQ$1799,79),"-")

    apart for a moment, and call it X. The formula then reduces to

    =IF(ISERROR(X),"",X)

    In other words, if X results in an error, return a blank, otherwise return X. You're well familiar with this construction by now. The expression

    VLOOKUP('Enter formula'!$H7,'Raw materials'!$A$2:$CQ$1799,95,FALSE)

    looks for the value of cell H7 on the sheet ' Enter Formula' in the first column of the range A2:CQ1799 on the sheet 'Raw Materials'. The last argument FALSE means that the match must be exact, not approximate. If found, the expression returns the value from the 95th column of the range, i.e. column CQ. If this value is "colorant", the expression

    VLOOKUP('Enter formula'!$H7,'Raw materials'!$A$2:$CQ$1799,79)

    then returns the value from the 79th column, i.e. column CA. If the value in column CQ is different from "colorant", return "-".

    In other words:
    <UL><LI>Look up the value of 'Enter Formula'!H7 in the first column of 'Raw materials'!A2:CQ1799.
    <LI>If not found, return a blank "".
    <LI>If found, look up value in the same row in column CQ.
    <LI>If this is "colorant", return the value from the same row in column CA.
    <LI>Otherwise, return "-".[/list]Clear as mud now? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Formula (e)

    One way to help decipher these is to break it up like a VB IF statement:
    Sorry, have to use <!t>[pre]<!/t> tags here for this to be readable on the Lounge
    <pre>=IF(ISERROR
    (IF((VLOOKUP('Enter formula'!$H7,'Raw materials'!$A$2:$CQ$1799,95,FALSE)="colorant"), 'ISERROR=TRUE
    VLOOKUP('Enter formula'!$H7,'Raw materials'!$A$2:$CQ$1799,79),"-")), '=colorant IS TRUE
    "", '=colorant IS FALSE
    IF((VLOOKUP('Enter formula'!$H7,'Raw materials'!$A$2:$CQ$1799,95,FALSE)="colorant"), 'ISERROR=FALSE
    VLOOKUP('Enter formula'!$H7,'Raw materials'!$A$2:$CQ$1799,79),"-") '=colorant IS TRUE
    )
    </pre>

    Another is to start with the deepest nested block and work outwards. Something like:

    BLOCK A

    IF((VLOOKUP('Enter formula'!$H7,'Raw materials'!$A$2:$CQ$1799,95,FALSE)=TRUE
    THEN
    "colorant"
    OTHERWISE
    VLOOKUP('Enter formula'!$H7,'Raw materials'!$A$2:$CQ$1799,79),"-")

    IF BLOCK A RESULTS IN AN ERROR i.e. ISERROR=TRUE
    THEN
    ""
    OTHERWISE
    EVALUATE BLOCK B:

    IF(VLOOKUP('Enter formula'!$H7,'Raw materials'!$A$2:$CQ$1799,95,FALSE)="colorant")=TRU E
    THEN
    VLOOKUP('Enter formula'!$H7,'Raw materials'!$A$2:$CQ$1799,79),"-")

    Alan

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Help with Formula (e)

    Hi Hans

    Thanks to you and Alan for the reply.

    This sheet is inherited from a person who is no longer with us, and there are dozens of formulas that I don't understand. so thanks for the explanation,

    Just as a bye the bye, just a yes no answer will suffice is this the best way to write this formula?

    Braddy
    If you are a fool at forty, you will always be a fool

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with Formula (e)

    There is some redundancy in the ISERROR test, you only need to check the first VLOOKUP:

    =IF(ISERROR(VLOOKUP('Enter formula'!$H7,'Raw materials'!$A$2:$CQ$1799,95,FALSE)),"",IF((VLOOKUP ('Enter formula'!$H7,'Raw materials'!$A$2:$CQ$1799,95,FALSE)="colorant"),VLO OKUP('Enter formula'!$H7,'Raw materials'!$A$2:$CQ$1799,79),"-"))

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Help with Formula (e)

    Hi Hans

    Thanks for your reply.

    Braddy
    If you are a fool at forty, you will always be a fool

  7. #7
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Formula (e)

    The last VLOOKUP (in the original) seems to suggest that 'Raw materials'!$A$2:$CQ$1799 is sorted in ascending order on its first column...

    What follows might be more efficient while compact...

    [1] In case the lookup area on Raw materials is sorted:

    =IF(LOOKUP(REPT("z",255),CHOOSE({1,2},"",VLOOKUP(' Enter formula'!$H7,'Raw materials'!$A$2:$CQ$1799,95)))="colorant",VLOOKUP( 'Enter formula'!$H7,'Raw materials'!$A$2:$CQ$1799,79),"-")

    [2] For unsorted data:

    =IF(LOOKUP(REPT("z",255),CHOOSE({1,2},"",VLOOKUP(' Enter formula'!$H7,'Raw materials'!$A$2:$CQ$1799,95,0)))="colorant",VLOOKU P('Enter formula'!$H7,'Raw materials'!$A$2:$CQ$1799,79,0),"-")
    Microsoft MVP - Excel

Posting Permissions

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