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

Hi,

Welcome to the Board!

I have made a little bit crazy formula.
The instruction is as follows:
1) Fill manually X in the first two rows of column B (because they have to be X no matter what is there in columns A,C,D)
2) Put formula =IFS(A3<>A2;"X";A3<>A1;"X";INDEX(D$1:D2;MAX(($F$1:F2="X")*ROW($F$1:F2))-1)<C3;"X";AND(A3=A2;A3=A1;F2="X";F1<>"X");"X";TRUE();"Y") in B3
3) Confirm it with Ctrl-Shift-Enter!!!
4) Test on your examples and let me know if it works as desired or not. In the latter case please send me an example where it works wrong.

J.Ty.

 

[stopo]

Hi,

Welcome to the Board!

I have made a little bit crazy formula.
The instruction is as follows:
1) Fill manually X in the first two rows of column B (because they have to be X no matter what is there in columns A,C,D)
2) Put formula =IFS(A3<>A2;"X";A3<>A1;"X";INDEX(D$1:D2;MAX(($F$1:F2="X")*ROW($F$1:F2))-1)<C3;"X";AND(A3=A2;A3=A1;F2="X";F1<>"X");"X";TRUE();"Y") in B3
3) Confirm it with Ctrl-Shift-Enter!!!
4) Test on your examples and let me know if it works as desired or not. In the latter case please send me an example where it works wrong.

J.Ty.

Hi

Thx the fast reply. Maybe I was not exact as I should be. The F Column is only for to explain what needed. The data range is A:D. In A1 and A2 just in this sample is the same. Could it be different in my working sheet.

 

[J.Ty.]

Sorry, my fault. I was programming the solution in column F and forgot to convert it into column B.
It should have been
=IFS(A3<>A2;"X";A3<>A1;"X";INDEX(D$1:D2;MAX(($B$1:B2="X")*ROW($B$1:B2))-1)<C3;"X";AND(A3=A2;A3=A1;B2="X";B1<>"X");"X";TRUE();"Y")

J.Ty.

 

[stopo]

Hi,

Welcome to the Board!

I have made a little bit crazy formula.
The instruction is as follows:
1) Fill manually X in the first two rows of column B (because they have to be X no matter what is there in columns A,C,D)
2) Put formula =IFS(A3<>A2;"X";A3<>A1;"X";INDEX(D$1:D2;MAX(($F$1:F2="X")*ROW($F$1:F2))-1)<C3;"X";AND(A3=A2;A3=A1;F2="X";F1<>"X");"X";TRUE();"Y") in B3
3) Confirm it with Ctrl-Shift-Enter!!!
4) Test on your examples and let me know if it works as desired or not. In the latter case please send me an example where it works wrong.

J.Ty.

Hi J,

The result:
X
X
Y
Y
Y but has to be X because C5>D1 or D2
/ if the date in C5=2020.01.13. than X must
be the result too /. Maybe MIN needed instead MAX.
X
X
#REF!
#REF!
#REF!
#REF!
#REF!

Thanks

Stopo

 

[J.Ty.]

Did you enter the formula and then confirm it with Ctrl-Shift-Enter? Excel then automatically places { } braces around and this is the way to check that you did it right.

J.Ty.

 

[stopo]

Did you enter the formula and then confirm it with Ctrl-Shift-Enter? Excel then automatically places { } braces around and this is the way to check that you did it right.

J.Ty.

Yes I did. I have to do "Update formula to include cells" so no more REF now. Result
X
X
Y
Y
Y still not good
X
X
X
X
X
X has to be Y
X has to be Y
X has to be Y
X

THX

 

[J.Ty.]

Could you please download XL2BB and use it to post your data? I need to experiment more and do not want to retype all your codes and dates from the image.

J.Ty.

 

[stopo]

No luck. IT dont allow to me download add on or anything else. Afternoon from home I can do it.

 

[J.Ty.]

OK, I'll wait.

J.Ty.