1

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

enter image description here

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?

Improve this question

1 Answer 1

Reset to default
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))

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Wow...works Great. Thank you... Will dig-in later how this works!
I'm little confused why/how the *(C10:C30=1) being used. Could you pls explain?
I assumed you might have duplicate values in D10:D30 (e.g. 1868 could occur more than once and maybe on rows where C10:C30 = 2), so you need to look for rows where 2 conditions are satisfied, i.e. D10:D30=B31 and also C10:C30=1 when you test those you get arrays of either TRUE or FALSE and when you multiply those arrays only TRUE*TRUE = 1 so MATCH looks for a 1 which equates to the first row where both conditions are met....and INDEX returns the corresponding row from G10:G30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.