using offset with array formulas

Q

QuantumPion

I have a formula that calculates a list of maximum values from another
sheet. In addition, I want to get the list of labels associated with
each of those items. The formula I am using is:

{=LARGE(IF(IF(ISNUMBER(Heat!G$2:G$1654),Heat!G$2:G$1654)<MIN(MIN(B$32:B$39),1100),IF(Heat!$C$2:$C$1654="",Heat!G$2:G$1654)),
ROW(B1))}

and what I want to do is basically just add a nested OFFSET(...,0,-7)
to that formula, where the "..." is the above formula in the
neighboring column. That way I will have the label for each item.
However this doesn't seem to work and excel gives me an error message.
Is it because the formula is an array? How can I get this to work then?
 
B

Biff

Hi!

If your large number is in column G and you want to offset that by 7 columns
to the left then your labels must be in column A:

Array entered:

=INDEX(Heat!A$2:A$1654,MATCH(LARGE(IF(ISNUMBER(Heat!G$2:G$1654),IF(Heat!G$2:G$1654<MIN(B$32:B$39,1100),IF(Heat!$C$2:$C$1654="",Heat!G$2:G$1654))),ROW(1:1)),Heat!G$2:G$1654,0))

Biff

"QuantumPion" <[email protected]>
wrote in message
news:[email protected]...
 
Top