Using =IF(ISNUMBER(SEARCH("SU",BK5)),"Special User" on a range of cells rather than just 1

[SI17]

Hi
From a previous thread (sorry I cannot locate it now) I have borrowed a function similar to this =IF(ISNUMBER(SEARCH("SU",BK5)),"Special User"

I am trying to see if cells contains SU the target cell is populated with Special user. I have also combined these into a series of if statements successfully to look for other conditions.

=IF(ISNUMBER(SEARCH("SU",BK5)),"Special User",IF(ISNUMBER(SEARCH("p",BK5)),"Prof user",IF(ISNUMBER(SEARCH("LP",BK5)),"Limited Prof user","")))

However I now need to apply this to a range of cells not just BK5. I have tried this with a valid number range by using BK1:BK5 but it returns no error or results even though BK5 is populated with a "p".

I would very much appreciate suggested resolution......

Thanks
Si

[daddylonglegs]

Hello Si, welcome to Excel Forum

What do you mean you want to apply to a range of cells? Do you mean you want a single result from that formula or are you trying to populate a range of 5 cells? If it's the former what result do you need?

[JBeaucaire]

String those cell values together, then test them.

=IF(ISNUMBER(SEARCH("SU",BK1&"-"&BK2&"-"&BK3"&"-"&BK4&"-"&BK5)),"Special User", .....

Or...

=IF(ISNUMBER(MATCH("SU", BK1:BK5, 0)), "Special User", ....

[SI17]

Hi there Daddylonglegs!
Thank you for the prompt reply. Sorry for not being clear. I meant that I want the if statement to 'look' at a range of cells and ideally the result be shown in one cell.

So the desired outcome would be that if one cell in the range of cells contained a "SU" and another cell contained a "p" and none of the cells in the range contained "LP" the target cell would:
1) Display "Special User, Prof user"
Otherwise
2) I could live with different target cells but that would be less good but I guess easier to achieve as I would just have the if statement in each cell.
In this case the expected results would be to have target cells, in 3 different columns one showing the result for Special user, one for Prof user and one for Limited Prof user in this case one cell would show Special user, one would show Prof user and the cell for Limited Prof user would be blank.

Hopefully that is clearer?
Many thanks
Simon

[daddylonglegs]

You could use Jerry's approach......or it might be easier to use COUNTIF, e.g.

=SUBSTITUTE(IF(COUNTIF(BK1:BK5,"SU"),", Special User","")&IF(COUNTIF(BK1:BK5,"p"),", Prof User","")&IF(COUNTIF(BK1:BK5,"LP"),", Limited Prof User",""),", ","",1)

Note that the SUBSTITUTE part is just to get the correct commas.....

I'm assuming that the whole cell contents is "SU", "p" etc.....or do you have other text in those cells? (use of SEARCH in your original formula implies you do)

Note that in your original version "LP" in BK5 will result in the text "Prof user" because of the "P" in "LP".....

[SI17]

Hi
Thanks to both responders. The most recent reply worked first time - thanks also for flagging the duplicate 'p' issue which is easily resolved!
Great forum.

Simon

[JBeaucaire]

If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.