nested case sensitive if formula

[ECB]

Hi all

I'm stuck again

I have a list of data of single letter values:

J
F
M
A
m
j
a
S
O
N
D

Which relates to January, February, March etc (There is no July)

Now, I want to write an if statement to convert the single text value to a number

eg:

=IF(L1="J",1,IF(L1="F",2,IF(L1="M",3,IF(L1="A",4,IF(L1="m",5)))))

Which doesn't work because the if is case insensitive - small m returns "3"

Anyone able to point out to me where I'm going wrong, or what would work instead?

TIA

 

[mikerickson]

Try

=IF(EXACT(L1,"J"), 1, IF(EXACT(L1,"F"),2,....

 

[Aladin Akyurek]

Hi all

I'm stuck again

I have a list of data of single letter values:

J
F
M
A
m
j
a
S
O
N
D

Which relates to January, February, March etc (There is no July)

Now, I want to write an if statement to convert the single text value to a number

eg:

=IF(L1="J",1,IF(L1="F",2,IF(L1="M",3,IF(L1="A",4,IF(L1="m",5)))))

Which doesn't work because the if is case insensitive - small m returns "3"

Anyone able to point out to me where I'm going wrong, or what would work instead?

TIA

Try...

Either:

=LOOKUP(9.99999999999999E+307,FIND({"J";"F";"M";"A";"m"},L1),{1;2;3;4;5})

Or:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
    LOOKUP(9.99999999999999E+307,FIND({"J";"F";"M";"A";"m"},L1),{1;2;3;4;5})))

The latter returns 0 in lieu of #N/A when necessary.

 

[Haseeb Avarakkan]

Hi, Try

Array Formula, Confirmed with Control+Shift+Enter, not just Enter

=MATCH(CODE(A1),{74;70;77;65;109;106;97;83;79;78;68},0)+OR(EXACT({"a","S","O","N","D"},A1))

 

[T. Valko]

Hi all

I'm stuck again

I have a list of data of single letter values:

J
F
M
A
m
j
a
S
O
N
D

Which relates to January, February, March etc (There is no July)

Now, I want to write an if statement to convert the single text value to a number

eg:

=IF(L1="J",1,IF(L1="F",2,IF(L1="M",3,IF(L1="A",4,IF(L1="m",5)))))

Which doesn't work because the if is case insensitive - small m returns "3"

Anyone able to point out to me where I'm going wrong, or what would work instead?

TIA

If there is no July then what value should be returned for "a" (August)?

Should it be 7 or 8?

If it should be 7, then:

=FIND(A1,"JFMAmjaSOND")

If it should be 8, then:

=FIND(A1,"JFMAmjaSOND")+(FIND(A1,"JFMAmjaSOND")>6)

 

[Haseeb Avarakkan]

If it should be 8, then:

=FIND(A1,"JFMAmjaSOND")+(FIND(A1,"JFMAmjaSOND")>6)

Just a modification in Biff's formula. Since FIND looking for the first occurrence simply add a another "j",

=FIND(A1,"JFMAmjjaSOND")

I think should be Ok

 

[T. Valko]

Just a modification in Biff's formula. Since FIND looking for the first occurrence simply add a another "j",

=FIND(A1,"JFMAmjjaSOND")

I think should be Ok

Why didn't I think of that?

 

[ECB]

Guys (and Gals)

You astound me with your ingenuity as always.

Thank you all very much.

 

[T. Valko]

Guys (and Gals)

You astound me with your ingenuity as always.

Thank you all very much.

You're welcome. We appreciate the feedback!