need help with If ,Index and Match

[shahdelsol]

I am not sure what I am trying to do will be resolved by IF and Index and Match. Here is the scenario. I am matching value in B7 with N7:N13, Once found then looking for matching the value of C5 in O7:013, if match found, 1 , if False 2
One caveat is the value in C5 is the name of weekdays like Monday, Tuesday etc. the cells in O7:O13 could have two weekdays in one cell like Monday, Tuesday. So if C5 says Monday but O7 says Monday, Tuesday for me it is a match. I hope someone can help. Thanks


=IF(INDEX(N6:O13,MATCH(B7,N6:N13,0),MATCH(C5,O6:O13)),1,2)

 

[AhoyNC]

Try:

Book1
	A	B	C	D	E								M	N	O
4
5			Saturday
6
7		test3		1										test1	Monday, Tuesday
8														test2	Wednesday, Thursday
9														test3	Friday, Saturday
10														test4	Sunday
11														test5	Tuesday, Thursday
12														test6	Friday, Saturday
13														test7	Sunday, Monday
Sheet3

Cell Formulas
Range	Formula
D7	D7	=IF(ISNUMBER(SEARCH(C5,INDEX(O7:O13,MATCH(B7,N7:N13,0)))),1,2)

 

[shahdelsol]

This kinda work however didn't work with my C5 formula. I already have this formula "={INDEX(calendar,,daypattern)}" in C5 and somehow your formula doesn't return proper result even my formula shows the exact weekday. If I remove formula from C5 and type in weekdays manually then it works. What do you suggest?

Try:

Book1
	A	B	C	D	E								M	N	O
4
5			Saturday
6
7		test3		1										test1	Monday, Tuesday
8														test2	Wednesday, Thursday
9														test3	Friday, Saturday
10														test4	Sunday
11														test5	Tuesday, Thursday
12														test6	Friday, Saturday
13														test7	Sunday, Monday
Sheet3

Cell Formulas
Range	Formula
D7	D7	=IF(ISNUMBER(SEARCH(C5,INDEX(O7:O13,MATCH(B7,N7:N13,0)))),1,2)

 

[AhoyNC]

Does it give the wrong answer or an error msg?

 

[shahdelsol]

It gives me wrong answer because I think it doesn't see match rather it sees my formula which is obviously not a match basically the answer is always 2

Does it give the wrong answer or an error msg?

 

[AhoyNC]

Could it be that the answer from your formula returns in spaces around the weekday?
If so try:

Book1
	A	B	C	D	E								M	N	O
5			Saturday
6
7		test3		1										test1	Monday, Tuesday
8														test2	Wednesday, Thursday
9														test3	Friday, Saturday
10														test4	Sunday
11														test5	Tuesday, Thursday
12														test6	Friday, Saturday
13														test7	Sunday, Monday
Sheet3

Cell Formulas
Range	Formula
D7	D7	=IF(ISNUMBER(SEARCH(TRIM(C5),INDEX(O7:O13,MATCH(B7,N7:N13,0)))),1,2)

 

[shahdelsol]

It didn't work and I don't think if there is any spaces or character or anything. It just seems it reads the actual formula in the cell nor the result.

Could it be that the answer from your formula returns in spaces around the weekday?
If so try:

Book1
	A	B	C	D	E								M	N	O
5			Saturday
6
7		test3		1										test1	Monday, Tuesday
8														test2	Wednesday, Thursday
9														test3	Friday, Saturday
10														test4	Sunday
11														test5	Tuesday, Thursday
12														test6	Friday, Saturday
13														test7	Sunday, Monday
Sheet3

Cell Formulas
Range	Formula
D7	D7	=IF(ISNUMBER(SEARCH(TRIM(C5),INDEX(O7:O13,MATCH(B7,N7:N13,0)))),1,2)

 

[AhoyNC]

Try changing your formula in C5 to
=TEXT(INDEX(calendar,,daypattern),"dddd")

 

[shahdelsol]

Thank you so much. It did work. I am reviewing the entire file and hopefully this change wouldn't affect other aspects. So far everything looks good.

Try changing your formula in C5 to
=TEXT(INDEX(calendar,,daypattern),"dddd")

 

[AhoyNC]

Good to hear that seems to be working. If you don't want to change the formula in C5, you could try changing the formula I gave you above to:

IF(ISNUMBER(SEARCH(TEXT(C5,"dddd"),INDEX(O7:O13,MATCH(B7,N7:N13,0)))),1,2)