VLOOKUP / index+match / with multiple criteria

[stopo]

Hi

Im very beginner with excel. I got a task with need help. I read forums now several days but I cannt got any solution yet. Try Vlookup index match formulas but always run into walls.

I need result for the B column. I already fill the B how it have to be look like and write the criteria into the excel too.
If a number appearence in A the first 2 is always X in B. Any more appearence in A will be Y in B until C<=D. / C is in Y row D is in the last row where X is. / If not the first 2 appearence start again.

I try this:

IFS(A1<>A0;"X";AND(A2=A1;A2<>A0);"X";AND(C2<=VLOOKUP(A2;A1:A1000;4;0));"Y";AND(C2>VLOOKUP(A2;A1:A1000;4;0));"X"

And this work for me until A1 first appearance period start again. Than the VLOOKUP dont work anymore because this give the first value in D. I try index , match , max , maxifs but cant
get any working well.

Thanks in advance If any help I can get.

Stopo

 

[J.Ty.]

One more idea: you can save your data from Excel as a CSV file and post here as such. That's good enough. I need only the rows you did tests on.

J.Ty.

 

[stopo]

OK, I'll wait.

J.Ty.

I think I got it! I put ORANGE letters to it and it working now. Now I will test it with my 10000 rows sheet I hope it will be good. THX YOUR HELP again! I am very grateful!

=IFS(A3<>A2;"X";A3<>A1;"X";INDEX(D$1$:D2;MAX(($B$1:B2="X")*ROW($B$1:B2))-1)<C2;"X";AND(A3=A2;A3=A1;B2="X";B1<>"X");"X";TRUE();"Y")

Stopo

 

[J.Ty.]

I'll keep my fingers crossed!

J.Ty.