Match weekday wise cities (stored in rows)

[gauravg1909]

I am having a following excel sheet (weekday wise cities, column wise):

					MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY	SUNDAY
					City1	City3	City5	City8	City12	City13	City15
					City2	City4	City6	City9		City14	City16
Date	Week Day	City	Status				City7	City10			City17
27-05-2024​	Monday​	City1	Ok					City11			City18
27-05-2024​	Monday​	City5	Wrong
28-05-2024​	Tuesday​	City18	Wrong
29-05-2024​	Wednesday​	City6	Ok
30-05-2024​	Thursday​	City12	Wrong
..........

I want a formula that check row wise data, day wise city matches or not (both check is compulsory week day+city).

If possible please help as early as possible.

Thanks in advance.

 

[pecoflyer]

Hi Please post a sample sheet. Pictures are quite useless. Thanks

 

[gauravg1909]

Hi Please post a sample sheet. Pictures are quite useless. Thanks

Hi, please find attached sample sheet

 

[AliGW]

Whcih version of Excel are you using?

 

[gauravg1909]

Whcih version of Excel are you using?

2021

 

[AliGW]

That's a shame! I was going to suggest this, but it's for 365:

=MAP(B5:B9,C5:C9,LAMBDA(x,y,IF(ISNUMBER(MATCH(y,FILTER(F2:L5,F1:L1=x),0)),"OK","Wrong")))

Might help someone else. Sorry!

 

[gauravg1909]

That's a shame! I was going to suggest this, but it's for 365:

=MAP(B5:B9,C5:C9,LAMBDA(x,y,IF(ISNUMBER(MATCH(y,FILTER(F2:L5,F1:L1=x),0)),"OK","Wrong")))

Might help someone else. Sorry!

Its Ok, but I using 2021, thanks for your help, if possible please try to find some solution in 2021

 

[gauravg1909]

Its Ok, but I using 2021, thanks for your help, if possible please try to find some solution in 2021

As I am unable to find a proper formula to do this, I solved my problem using concatenate, text & vlookup etc...., Plz check the attached file, waiting for reply.

 

[AliGW]

In B5 copied down:

=TEXT(A5,"dddd")

In D5 copied down:

=IF(ISNUMBER(MATCH(C5,FILTER($F$2:$L$5,$F$1:$L$1=B5),0)),"OK","Wrong")

 

[gauravg1909]

In B5 copied down:

=TEXT(A5,"dddd")

In D5 copied down:

=IF(ISNUMBER(MATCH(C5,FILTER($F$2:$L$5,$F$1:$L$1=B5),0)),"OK","Wrong")

WOW!!, its working, thanks for your help. Trying to check my actual data using your golden formulas, thanks again. You saved my a lot of time to do concatenate, text etc. etc.,,,,,,,,, thanks a lot again.

 

[AliGW]

You're welcome. Maybe you'd like to hit the LIKE button under my last post?