Previous Topic Next Topic

Excel formula question.

Closed
marengl1 Posts 1 Registration date Tuesday 7 June 2016 Status Member Last seen 7 June 2016 - 7 Jun 2016 à 13:41
RayH Posts 122 Registration date Tuesday 31 August 2010 Status Contributor Last seen 20 June 2016 - 8 Jun 2016 à 10:18
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.
Related:

1 response

Réponse 1 / 1
xpcman Posts 19528 Registration date Wednesday 8 October 2008 Status Contributor Last seen 15 June 2019 1,824
7 Jun 2016 à 16:01
ISNUMBER checks to see if the value is a number - not a text match.
Luc
7 Jun 2016 à 17:44
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.
RayH Posts 122 Registration date Tuesday 31 August 2010 Status Contributor Last seen 20 June 2016 26
7 Jun 2016 à 22:42
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")
Luc > RayH Posts 122 Registration date Tuesday 31 August 2010 Status Contributor Last seen 20 June 2016
8 Jun 2016 à 08:40
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:)
RayH Posts 122 Registration date Tuesday 31 August 2010 Status Contributor Last seen 20 June 2016 26 > Luc
8 Jun 2016 à 10:18
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.

Similar discussions

How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
How to calculate position (1st,2nd,3rd) in excel....?
abdulwajid -
TrowaD -

1 response
Calculation of positions in excel
Mohsin -
Dausayi -

12 responses
Keyboard troubles
jimbobjoe -
car -

38 responses