Depending on next cell value

[Mike7]

Hi.
In A2:AO101 I have numbers from 1 to 100
Please, help me to create a formula for AQ2 and down which would do following:
Look at the last cell of the range A2:AO2 which is AO2
then find the same value in that range (could be more than one or not present)
- return 0 if same value is not present in the row
- return 0 if the right side next value is < 41 at least one time
- return 1 if the right side next value is >40

Here are short examples:

12 67 43 2 86 43 90 7 3 77 15 31 67 22 43 78 55 - 0 (value 55 is present only in the last column)

75 6 37 91 65 49 6 82 22 32 76 32 75 87 11 99 6 - 0 ( found less than 41 value right after 6 (37))

53 29 8 61 53 1 16 98 29 53 41 37 52 33 19 61 61 - 1 ( all values right after 61 are greater than 40)

Thanks.

 

[Mike7]

Sorry, forgot to mention
To return 1 the value in AO2 must be >40
so in 2-nd example - found less than 41 value right after 6 (37) an the value 6 itself is not greater than 40