IF Formula replacement instead of using EXACT

[Beh162]

I have an IF formula I've been using that uses EXACT function in it, so anytime something was in CAPS and it matched a cell it would come back with a 2. If it matched but wasn't caps it would be 1. Then those numbers would be tallied.

I wanted to see if maybe there was another array I could use instead. I wish it would work with formatting where I could set up a conditional format where the CAPS would be rather than manually typing it caps, but I'm aware it doesn't.

IF(OR($S$3=""),"",IF(AND(K3="Push",$S$3="Push"),3,IF(AND(K3=$S$3,EXACT(K3,$S$3)),1,IF(K3=$S$3,2,0))))

background info:
It's a list of teams and you select one team as your "best bet" and if you get that right you get an extra point. I've been using CAPS to signify the best bet and the formula would return 2 points if it matched S3 and was caps.

 

[Aladin Akyurek]

Care to provide input values along with the output you require?

 

[Beh162]

Care to provide input values along with the output you require?

Input values are all text so what would appear in K3 "Jets" and the output would be either 0,1,2
If K3 matches S3 =1 point
if it doesn't match = 0

The tricky is the "best bet" I need to give one team that is selected a special value that if it matches they get 2 points. In my current formula it was JETS(Caps) that if it matched it would be 2.

Hope that makes sense

 

[Aladin Akyurek]

No, it doesn't. You are asked to post input values and the corresponding output.

K3 = jets

S3 = jets

>> what is the result?

K4 = jets

S4 = JETS

>> What is the result?

K5 = JETS

S5 = JETS

>> what is the result?

K6 = JETS

S6 =jets

>> What is the result?

 

[Beh162]

S3 is Jets (It will always be written this way)
K3 is JETS (caps is considered their best bet)
L3 will be 2

S3 is Jets
K3 is Jets
L3 is 1

S3 is Jets
K3 is Bills
L3 is 0

For the next person
s3 is Jets
L3 is Jets
M3 is 1

 

[Aladin Akyurek]

Does this help?

=IF(K3="","",(K3=S3)+EXACT(UPPER(K3),K3))

 

[Beh162]

Does this help?

=IF(K3="","",(K3=S3)+EXACT(UPPER(K3),K3))

Yes, that works... now what if I wanted to get away from having to put CAPS, is there another way?

 

[Aladin Akyurek]

Yes, that works... now what if I wanted to get away from having to put CAPS, is there another way?

How do you mean? Isn't K3 the cell which houses a user entry and the user "types" there his/her entry in capital letters?

 

[Beh162]

How do you mean? Isn't K3 the cell which houses a user entry and the user "types" there his/her entry in capital letters?

All is true except K3 is manually moved from regular to CAPS for the formula to work. I wanted to see if I could move away from that

 

[Aladin Akyurek]

All is true except K3 is manually moved from regular to CAPS for the formula to work. I wanted to see if I could move away from that

If changing K3 on demand to capitals is tiring, put a tag in a satellite cell like K2 in order to mark the entry in K3 as best bet. And modify the formula to work with that tag.