Results 1 to 7 of 7
Thread: Help with Formula (e)

20050827, 12:02 #1
 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
BraddyIf you are a fool at forty, you will always be a fool

20050827, 13:04 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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>

20050827, 13:31 #3
 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

20050827, 13:37 #4
 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?
BraddyIf you are a fool at forty, you will always be a fool

20050827, 13:57 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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),""))

20050827, 16:34 #6
 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.
BraddyIf you are a fool at forty, you will always be a fool

20050827, 21:20 #7
 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