Help with Formula (e)

Topic

New Reply

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”),VLOOKUP(‘Enter formula’!$H7,’Raw materials’!$A$2:$CQ$1799,79),”-“)),””,IF((VLOOKUP(‘Enter formula’!$H7,’Raw materials’!$A$2:$CQ$1799,95,FALSE)=”colorant”),VLOOKUP(‘Enter formula’!$H7,’Raw materials’!$A$2:$CQ$1799,79),”-“))

Many Thanks

Braddy

Reply | Quote

Replies

Let’s take the expression

IF((VLOOKUP(‘Enter formula’!$H7,’Raw materials’!$A$2:$CQ$1799,95,FALSE)=”colorant”),VLOOKUP(‘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:

• Look up the value of ‘Enter Formula’!H7 in the first column of ‘Raw materials’!A2:CQ1799.
• If not found, return a blank “”.
• If found, look up value in the same row in column CQ.
• If this is “colorant”, return the value from the same row in column CA.
• Otherwise, return “-“.
  [/list]Clear as mud now?

Reply | Quote

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

Reply | Quote

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”),VLOOKUP(‘Enter formula’!$H7,’Raw materials’!$A$2:$CQ$1799,79),”-“))

Reply | Quote

Hi Hans

Thanks for your reply.

Braddy

Reply | Quote

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”,VLOOKUP(‘Enter formula’!$H7,’Raw materials’!$A$2:$CQ$1799,79,0),”-“)

Reply | Quote

One way to help decipher these is to break it up like a VB IF statement:
Sorry, have to use tags here for this to be readable on the Lounge

=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
)

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”)=TRUE
THEN
VLOOKUP(‘Enter formula’!$H7,’Raw materials’!$A$2:$CQ$1799,79),”-“)

Alan

Reply | Quote