KuraiChikara
Board Regular
- Joined
- Nov 16, 2016
- Messages
- 111
- Office Version
- 2013
- Platform
- Windows
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---
<tbody>
</tbody>
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>