formula to check range, return a certain value and reset the range

[monoj chakraborty]

Hi all,
I am working with an excel file as attached. We are working with the following ranges :
1. Y:AA is the check range
2. AB:AG is the result range
3. U is the value column
what I am trying to achieve is as follows :
For every cell between AB:AG, we need to check the following :
> check Y:AA whether we have reached >=45
>in the next cell return U (from corresponding row)
>if any cell has reached U in the previous cell, it resets the search and match for 45 all over again from Y
example :
for row 23,
AB23, checks Y23 and finds >=45
AC23, checks AB23 and finds it has already reached >=45 and hence returns U23
AD23 checks AC23 and finds U23 and it checks Y23 to find >=45
AE23, finds >=45 in AD23 and returns U23 again
AF23 checks AE23 and finds U23 so it checks Y again to find >=45
AG23, finds 45 in AF23 and returns U23 again

its a complex logic and gpt4 has not been able to solve it successfully. Here's the code I was using, but it was solving for the 1st U value match but the subsequent reset and match was not happening :

=IF(COLUMN()-COLUMN($AB$4)+1 = MATCH(TRUE, $Y4:$AA4>=0.45, 0)+1, $U4, IF(AND(INDIRECT(ADDRESS(ROW(),COLUMN()-1))=$U4, COLUMN()-COLUMN($AB$4)+1 > MATCH(TRUE, $Y4:$AA4>=0.45, 0)), IFERROR(IF(MATCH(TRUE, $Y4:$AA4>=0.45, 0) = COLUMN()-COLUMN($AB$4), $U4, 0), 0), 0))

Will be very grateful if some kind soul picks this up and solves this

appreciate and best regds.,

 

[vletm]

monoj chakraborty​

Y23's value is 25%
You wrote AB23, checks Y23 and finds >=45
Is there somewhere clear logic which should use?