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

#### monoj chakraborty

##### Member
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 :

Code:
``=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.,

#### Attachments

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

#### monoj chakraborty​

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