Formula giving incorrect answer...

[Guest]

I have the following formula in cells B2:B16
=INDEX(Schedule!$B$1:$B$1300,SMALL(IF(Schedule!$A$1:$A$1300=$A$1,ROW(Schedule!$A$1:$A$1300)),1))

That formula works fine. However, I have the following formula in cells
A2:A16
=IF(OR(B2="BAL",B2="BOS",B2="NYY",B2="TB",B2="TOR",B2="CWS",B2="CLE",B2="DET",B2="KC",B2="MIN",B2="LAA",B2="OAK",B2="SEA",B2="TEX"),"AL","NL")

For some reason I get NL in all of the column A cells even if the cells in B
are BAL, BOS, NYY, etc. If I just type in BAL (and overwrite the formula)
in cell B2, cell A2 will correctly give me AL. Any ideas why it is doing
this? Thanks

 

[Arvi Laanemets]

Hi

Probably you had trailing blanks in column B, i.e. the formula p.e. in B2
doesn't return "BAL", but " BAL" or "BAL " or "BAL ", ...
When this is the case, remove trailing blanks from Shedule!B1:B1300 (select
the range, ReplaceAll " " with "")

You also can use TRIM in your IF() contitions. Here is somewhat simpler
formula, where you don't have to use the TRIM for every condition
separately:
=IF(ISNA(MATCH(TRIM(B2),{"BAL";"BOS";"NYY";"TB";"TOR";"CWS";"CLE";"DET";"KC"
;"MIN";"LAA";"OAK";"SEA";"TEX"},0)),"NL","AL")

 

[Ron Rosenfeld]

I have the following formula in cells B2:B163
=INDEX(Schedule!$B$1:$B$1300,SMALL(IF(Schedule!$A$1:$A$1300=$A$1,ROW(Schedule!$A$1:$A$1300)),1))

That formula works fine. However, I have the following formula in cells
A2:A163
=IF(OR(B2="BAL",B2="BOS",B2="NYY",B2="TB",B2="TOR",B2="CWS",B2="CLE",B2="DET",B2="KC",B2="MIN",B2="LAA",B2="OAK",B2="SEA",B2="TEX"),"AL","NL")

For some reason I get NL in all of the column A cells even if the cells in B
are BAL, BOS, NYY, etc. If I just type in BAL (and overwrite the formula)
in cell B2, cell A2 will correctly give me AL. Any ideas why it is doing
this? Thanks

Most likely, the formula in B2 is not returning "exactly" "BAL". Perhaps there
is an extra space or some other non-printing character in Schedule!


--ron

 

[Guest]

Thank you all for the help. Arvi, when I pasted your trim formula in it
worked perfectly. Thanks again.