IF AND function help: Differentiate with Similar Criteria

[KuraiChikara]

Hello,
I have an issue with an IF AND function that contains similar criteria.
Example: Agents have their days off and their week is formatted. IE: Tuesday/Wednesday off will look like this FYSM--R.

Some agents have 3 days off, Tuesday/Wednesday/Thursday off will look like this FYSM---

I have an IF AND formula to format this, but it's that 3rd day off that's causing issues.

Because the agent with the 3rd day off ALSO has Tu/We off, when I put in my formula, FYSM--R is put instead of FYSM---.

I don't know how to get Excel to differentiate "Oh he has Tuesday, Wednesday AND Thursdays off, put FYSM---" Instead, it sees the first matching criteria and puts FYSM--R instead.

Grid explanation:
Everything RIGHT of WEEK FORMULA (Column E) is the reference data for my formula

=IF(AND(B2=$J$1,C2=$K$1),$F$2,IF(AND(B3=$J$1,C3=$K$1,D3=$L$1),$F$3,"NO MATCH"))

The result I want:
John should have FYSM--R
Bill should have FYSM---

[TABLE="class: grid, width: 600, align: center"]
<tbody>[TR]
[TD]Agent[/TD]
[TD]Off1[/TD]
[TD]Off2[/TD]
[TD]Off3[/TD]
[TD]Week Formula[/TD]
[TD]Weeks[/TD]
[TD]Sa[/TD]
[TD]Su[/TD]
[TD]Mo[/TD]
[TD]Tu[/TD]
[TD]We[/TD]
[TD]Th[/TD]
[TD]Fr[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Tu[/TD]
[TD]We[/TD]
[TD][/TD]
[TD]FYSM--R[/TD]
[TD]FYSM--R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]Tu[/TD]
[TD]We[/TD]
[TD]Th[/TD]
[TD]FYSM--R[/TD]
[TD]FYSM---[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

Just reverse the order of the IF tests:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Agent	Off1	Off2	Off3	Week Formula	Weeks	Sa	Su	Mo	Tu	We	Th	Fr
2	John	Tu	We		FYSM--R	FYSM--R
3	Bill	Tu	We	Th	FYSM---	FYSM---
Sheet215

Cell Formulas
Range	Formula
E2	=IF(AND(B2=$J$1,C2=$K$1,D2=$L$1),$F$3,IF(AND(B2=$J$1,C2=$K$1),$F$2,"NO MATCH"))

 

[KuraiChikara]

Awesome! Super simple too! I think I over complicate things.

 

[jeremypyle]

Hi,

Just reverse the order of the IF tests:

	A	B	C	D	E	F	G	H	I	J	K	L	M
Agent	Off1	Off2	Off3	Week Formula	Weeks	Sa	Su	Mo	Tu	We	Th	Fr
John	Tu	We	FYSM--R	FYSM--R
Bill	Tu	We	Th	FYSM---	FYSM---

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet215​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=IF(AND(B2=$J$1,C2=$K$1,D2=$L$1),$F$3,IF(AND(B2=$J$1,C2=$K$1),$F$2,"NO MATCH"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

How do you copy and paste pictures of your excel spreadsheet like you have jtakw?

 

[jtakw]

How do you copy and paste pictures of your excel spreadsheet like you have jtakw?

Hi,

Read Part B here: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

and Post 2 here: https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[KuraiChikara]

I actually have the same question as a the user before me, how do you insert excel images? My examples would be much better if I could do that. The only way I've been able to think of how to do it on this site is to use a photo sharing site to upload images and use the insert image page here. Is that how you do it?

 

[KuraiChikara]

You just responded to my question too. Ignore the previous inquiry.

 

[jtakw]

Awesome! Super simple too! I think I over complicate things.

Thanks for the feedback.

You just responded to my question too. Ignore the previous inquiry.

If you want to Test one or more of the Posting Table methods, you can do them here: https://www.mrexcel.com/forum/test-here/