# Thread: Help with Formula (e)

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

2. ## 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 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. ## 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. ## 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?

5. ## 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),"-"))

Hi Hans

7. ## 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),"-")

#### Posting Permissions

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