Excel SEARCH with multiple lookup values 1

[Bobo72]

Hi all,

I'm trying to look through multiple values in a cell and find out if the cell contains one of more values (OR):

Cell to search (example - cell A1): R10 R40 R44 R56 (text value)
Search function (doesn't work!): =IF(ISERROR(SEARCH("R10" OR "R30" OR "R88");"no match";"match ok"))

My question is: How do I make this work? I cannot use OR, neither '+'? Perhaps I can pass an array as parameter? Anyway I do it, Excel needs to know that as long as it finds one or more of the parameters in the 'array', there is a match.

Br and thanks,
Bo

 

[GlennUK]

Your SEARCH function doesn't mention what to search, and usage of OR is by way of a function. So, your formula should look like this:

=IF(OR(NOT(ISERROR(SEARCH("R10",A1))),NOT(ISERROR(SEARCH("R30",A1))),NOT(ISERROR(SEARCH("R88",A1)))),"match ok","no match")

but I prefer using SUMPRODUCT, like this:

=IF(SUMPRODUCT(--NOT(ISERROR(SEARCH({"R10","R30","R88"},A1))))=0,"no match","match ok")

as you get an overall simpler formula.



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Bobo72]

Hi Glenn,

Thank you for a fast answer : ) I tried both options, and I also find that SUMPRODUCT is well suited and works fine. However, I have to use ';' instead of ',' inside the array and between the array and the 'search in field'.

Just for future reference; what does "--NOT" mean?

Br Bo

 

[xlbo]

-- is a value coercion
NOT reverses any logic it precedes

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question