Trouble with IF / Match Statement - Can't Generate the Correct Result. ( solved )

[david gabra]

Hi

I have tried so many options, so many variation, and can't do it. I prefer excel formulae rather than macro is possible.
I really appreciate your expertise.

1) To populate AD2 - Please add formulae in AD

I need a formula that should now find the first cell in the range F2:X2 that has a value greater than or equal to D2 and returns the correct name from the preceding cell (e.g., if H2 meets the condition, it will return the value in G2).

2) To populate AE2 - Please add formulae in AE

I need a formula that should now find the second cell (continue from where AD2 stopped searching) in the range F2:X2 that has a value greater than or equal to D2 and returns the correct name from the preceding cell (e.g., if T2 meets the condition, it will return the value in S2).

I have a sample table of the results we want to automate - this is in cells AH and AI.

Please refer to the attached excel.

Thank you in advance so much for time and effort helping.

Dave.

 

[p45cal]

In AD2:

=INDEX(E2:W2,MATCH(TRUE,(F2:X2-D2)>=0,0))

copy down.
In AE2:

=INDEX(E2:W2,AGGREGATE(15,6,IF((F2:X2-D2)>=0,SEQUENCE(,19)),2))

copy down.

 

[david gabra]

Thank you. I tested on many scenarios, and it worked.
A million thanks.

 

[david gabra]

Please close this post.

 

[pecoflyer]

There is no option on this forum to close the post.

 

[david gabra]

Thank you for the update