If and criteria formula

[louis128]

what i really need is that formula IN L1 , which will work like:

if in K1 is a number >=1 and in J:J the last two numbers are 2 1 and the third from the end is not 3 show at L1 number 3 ,
if in K1 >=1 and in J:J the last two numbers are 1 2 and the third from the end is not 3 show at L1 number 3 ,
if in K1 >=1 and in J:J the last two numbers are 2 3 and the third from the end is not 1 show at L1 number 1,
if in K1 >1 and in J:J the last two numbers are 3 2 and the third from the end is not 1 show at L1 number 1 ,
if in K1 >=1 and in J:J the last two numbers are 3 1 and the third from the end is not 2 show at L1 number 2 ,
if in K1 >=1 and in J:J the last two numbers are 1 3 and the third from the end is not 2 show at L1 number 2 .

I will be so thankful,if you can help me . i know that is not easy.

[teylyn]

Hello,

It's not quite clear what the order of the numbers is or should be, but here are a few pointers:

get the last number of J:J =INDEX(J:J,MATCH(99^99,J:J,1))
get the second last number of J:J =INDEX(J:J,MATCH(99^99,J:J,1)-1)
get the third last number of J:J =INDEX(J:J,MATCH(99^99,J:J,1)-2)

So, now you just need to create a nested IF statement. You can combine conditions with the AND() function.

Please Login or Register  to view this content.

[Bo_Ry]

Please attached file with the sample and the expected result.

[louis128]

i just made it.is working! thanks so much

[Bo_Ry]

That's Great.

I have the shortened version.

=IF(K1>2,IFERROR(MATCH(1,--ISERROR(MATCH({1,2,3},INDEX(J:J,N(IF(1,COUNT(J:J)-{0,1,2}))),)),),),)