Excel Array Formula and combination

Question

Need to get data based on the results returned by array formula

Now I need to get:

• Find highest of Column(D) where Column(C)=1. "Value->1868". (i.e between 16:00-18:00 Hrs,highest for 1)
• From the same row of above result, get the column values for Column(G)->1256 and (H)->57

To get the first - highest of Column(D), I have the following Array formula which works fine. =MAX(IF((C10:C30)=1,D10:D30))

Now based on this, how do I get the highest of Col-G and H?

Answer 1

You can use INDEX/MATCH to do that, e.g. for column G value try this "array formula"

=INDEX(G10:G30,MATCH(1,(D10:D30=MAX(IF(C10:C30=1,D10:D30)))*(C10:C30=1),0))

confirmed with CTRL+SHIFT+ENTER

replace G10:G30 with H10:H30 to gt the column H value

of course if you already have your formula in B31 you can use that result in my suggested formula to simplify, i.e.

=INDEX(G10:G30,MATCH(1,(D10:D30=B31)*(C10:C30=1),0))