Match date with values on lists (with conditions on each list)

[RonOliver]

Hi.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J
1				Dragonfruit	Pear	Mango	Banana	Apple	Strawberry	Pomegranate
2
3				10/31/22	10/31/22	10/31/22	09/29/22	10/31/22	09/26/22
4	MON/WED	Strawberry		02/03/23	12/05/22	03/16/23	10/31/22	02/03/23
5	TUE/THU	Banana		03/16/23	03/17/23	03/17/23	12/05/22	03/16/23
6		09/26/22		03/17/23			03/17/23	03/17/23
7
8
9	True or false?
10
Sheet1

Let me tell you what I'm looking for:

I need a formula that checks if the date in B6 matches any of the dates from the range on the right, and then returns "TRUE" if the column where the matching date is located also matches B4 (if the date is a Monday or a Wednesday) or B5 (if the date is a Tuesday or a Thursday). If it doesn't, I'd like for it to return "FALSE". If this is too hard or convoluted, I would also be happy with the formula if it doesn't check what the date is, as long as it matches either B4 or B5. Thanks for your help!

 

[AhoyNC]

I'm not sure I understand what you are trying to do. Both can't be true as the day of the week can only match one of the items.
Maybe something like this, if you are looking at them individually?

Book3
	A	B	C	D	E	F	G	H	I	J
1				Dragonfruit	Pear	Mango	Banana	Apple	Strawberry	Pomegranate
2
3				10/31/2022	10/31/2022	10/31/2022	11/29/2022	10/31/2022	9/26/2022
4	MON/WED	Strawberry	TRUE	2/3/2023	12/5/2022	3/16/2023	10/31/2022	2/3/2023
5	TUE/THU	Banana	FALSE	3/16/2023	3/17/2023	3/17/2023	12/5/2022	3/16/2023
6		9/26/2022		3/17/2023			9/26/2022	3/17/2023
Sheet1

Cell Formulas
Range	Formula
C4:C5	C4	=IF(ISNUMBER(SEARCH(TEXT($B$6,"ddd"),A4)),IF(FILTER(FILTER($D$3:$J$6,$D$1:$J$1=B4,"FALSE"),FILTER($D$3:$J$6,$D$1:$J$1=B4,"FALSE")=$B$6,"FALSE"),TRUE,FALSE))

 

[RonOliver]

I'm not sure I understand what you are trying to do. Both can't be true as the day of the week can only match one of the items.
Maybe something like this, if you are looking at them individually?

Book3
	A	B	C	D	E	F	G	H	I	J
1				Dragonfruit	Pear	Mango	Banana	Apple	Strawberry	Pomegranate
2
3				10/31/2022	10/31/2022	10/31/2022	11/29/2022	10/31/2022	9/26/2022
4	MON/WED	Strawberry	TRUE	2/3/2023	12/5/2022	3/16/2023	10/31/2022	2/3/2023
5	TUE/THU	Banana	FALSE	3/16/2023	3/17/2023	3/17/2023	12/5/2022	3/16/2023
6		9/26/2022		3/17/2023			9/26/2022	3/17/2023
Sheet1

Cell Formulas
Range	Formula
C4:C5	C4	=IF(ISNUMBER(SEARCH(TEXT($B$6,"ddd"),A4)),IF(FILTER(FILTER($D$3:$J$6,$D$1:$J$1=B4,"FALSE"),FILTER($D$3:$J$6,$D$1:$J$1=B4,"FALSE")=$B$6,"FALSE"),TRUE,FALSE))

Yes. I need something like that, but in just one formula. Here is my attempt (Mind you I'm very inexperience, so the code is probably going to be atrocious):

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J
1				Dragonfruit	Pear	Mango	Banana	Apple	Strawberry	Pomegranate
2
3				10/31/22	10/31/22	10/31/22	09/29/22	10/31/22	09/26/22
4	MON/WED	Strawberry		02/03/23	12/05/22	03/16/23	10/31/22	02/03/23
5	TUE/THU	Banana		03/16/23	03/17/23	03/17/23	12/05/22	03/16/23
6		09/26/22		03/17/23			03/17/23	03/17/23
7
8
9	True or false?	TRUE
10
Sheet1

Cell Formulas
Range	Formula
B9	B9	=IF(OR(AND(OR(WEEKDAY(B6,2)=1,WEEKDAY(B6,2)=3),OR(HLOOKUP($B$4,$D$1:$J$10,{3,4,5,6,7,8,9,10},0)=B6)),AND(OR(WEEKDAY(B6,2)=2,WEEKDAY(B6,2)=4),OR(HLOOKUP($B$5,$D$1:$J$10,{3,4,5,6,7,8,9,10},0)=B6))),TRUE,FALSE)

If there is a way to shorten it or improve it in any way, I would really appreciate it.

 

[Fluff]

How about

=ISNUMBER(MATCH(B6,CHOOSECOLS(D3:J6,MAX(IFNA(MATCH(IF(ISNUMBER(SEARCH(TEXT(B6,"ddd"),A4:A5)),B4:B5,0),D1:J1,0),0))),0))

 

[RonOliver]

How about

=ISNUMBER(MATCH(B6,CHOOSECOLS(D3:J6,MAX(IFNA(MATCH(IF(ISNUMBER(SEARCH(TEXT(B6,"ddd"),A4:A5)),B4:B5,0),D1:J1,0),0))),0))

OK, now this is disturbing. I don't even know how you did that without WEEKDAY. I don't understand the formula at all, but I've tested it for a few minutes and it amazingly works. What kind of sorcery is this? Thank you so very much. I'll spend the next few weeks trying to figure out how the formula works and replace my own formula.

 

[Fluff]

Glad we could help & thanks for the feedback.