• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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.,


  • gtm^2 gpt v.1.xlsx
    33.7 KB · Views: 3