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---

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--R

FYSM---

<tbody>
</tbody>

 

[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
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---

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet215​

Worksheet Formulas Cell 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")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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/