Excel formula question. [Closed]

Report
Posts
1
Registration date
Tuesday June 7, 2016
Status
Member
Last seen
June 7, 2016
-
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
-
I have these cells info.

CBSA ; RCMP
EC ; FO ; HCAN ; NATRES ; ND
CRA ; PSC ; PWGSC
ND ; PWGSC
ACOA ; CFIA ; CRA ; CSC ; FO ; HRSDC ; PWGSC


Trying to do a formula in order to select the cells that has the partner name as follow:

RCMP, CBSA, CRA, ND, CFIA, IC and mark as PRIO but for some reason a portion is working and I am wondering why?

=IF(OR(ISNUMBER(SEARCH("RCMP",C3)),ISNUMBER(SEARCH("CBSA",C3)),ISNUMBER(SEARCH("CRA",C3)),ISNUMBER(SEARCH("ND",C3)),ISNUMBER(SEARCH("CFIA",C3)),ISNUMBER(SEARCH("IC",C3))),"PRIO"," ")

It work but not sure why the program gives me a PRIO when the cell contain IAND and in the formula I ask for "ND" NOT IAND so what is wrong with the formula?

I thought by using the ISNUMBER it would give me the exact match which I can see here that it's not why?


Thanks.

1 reply

Posts
19532
Registration date
Wednesday October 8, 2008
Status
Contributor
Last seen
June 15, 2019
1,824
ISNUMBER checks to see if the value is a number - not a text match.
actually my cell format was set to general. tried text, tried number still no go.
didn't know if it matter but I even tried ISTEXT got worst :))

Thanks for your time.
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
24
If I have understood your request this should work.

=IF(IFERROR(OR(SEARCH("RCMP",C3),SEARCH("CBSA",C3),SEARCH("CRA",C3),SEARCH("ND",C3),SEARCH("CFIA",C3),SEARCH("IC",C3)),FALSE),"PRIO","Not Found")
>
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016

Thanks Ray but what I am trying to do is select the cell with only the name I want and have the word PRIO written but your formula works if I change the false to true same result if I'm using my formula with the ISNUMBER but bottom line I think the issue here is when we are using the command SEARCH the search look for "RCMP" and if it sees one it will flag it right away but when I ask for the work ND and in the cell info I have IAND it will still flag it cuz ND is part of the word that's not what I want.

Not to forget that my cell info look like this:

EC ; CRA ; FO ; RCMP ; NATRES ; IAND

So I think there is no answer to this due to the limitation of the word SEARCH.
not sure if in excel there is another word that can isolate each word and be able to do a better triage.

Thanks for your time Ray.

Appreciated.

Luc:)
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
24 > Luc
Sorry Luc I was fixated on the formula rather than the data.

I found a similar question here and while it does not actually solve your problem regarding ND vs IAND if does offer a slightly different solution using array formulas and look-up lists.
.
http://stackoverflow.com/questions/5394063/if-cell-contains-1-or-more-keywords-change-value-of-a-different-cell


Can the values ND or IAND be changed to something unique as this would solve the problem immediately?
Could ND and IAND be in the same cell or are these mutually exclusive?

I'll have another look and see if I can come up with anything else.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!