Searching for strings in a column

[circuit_breaker]

Hi,
I have the following items in COL_A:

COL_A
-------
Mike B
GX270
Notebook X
LunchRoom
New Printer
John VPN
Spare Laptop
Susan WS

What I'd like to do, is to set COL_B to "TRUE" when COL_A contains:
"notebook" or "VPN" or "Laptop", in a non case-sensitive context.

The result should look like this:

COL_A COL_B
------- -------
Mike B FALSE
GX270 FALSE
Notebook X TRUE
LunchRoom FALSE
New Printer FALSE
John VPN TRUE
Spare Laptop TRUE
Susan WS FALSE

I've been trying a VLOOKUP() function but it doesn't work as
expected. The MATCH() funciton works but makes a very complicated
formula and I'll have to add exceptions to my list so, putting
"Laptop", "VPN", "notebook" and so on in a seperate worksheet makes
sense.

Thanks for your suggestions.

 

[Pete_UK]

Enter these words in the cells stated:

D1: notebook
D2: VPN
D3: Laptop

then put this formula in B1:

=IF(OR(ISNUMBER(SEARCH(D$1,A1)),ISNUMBER(SEARCH(D$2,A1)),ISNUMBER
(SEARCH(D$3,A1))),TRUE,FALSE)

and copy down to B8.

Hope this helps.

Pete

 

[circuit_breaker]

Well, it surely works, thanks.

Enter these words in the cells stated:

D1:   notebook
D2:   VPN
D3:   Laptop

then put this formula in B1:

=IF(OR(ISNUMBER(SEARCH(D$1,A1)),ISNUMBER(SEARCH(D$2,A1)),ISNUMBER
(SEARCH(D$3,A1))),TRUE,FALSE)

and copy down to B8.

Hope this helps.

Pete









- Show quoted text -

 

[Teethless mama]

Try this:
=SUMPRODUCT(--ISNUMBER(SEARCH({"Laptop","VPN","notebook"},A1)))>0

or this:
=SUM(COUNTIF(A1,"*"&{"Laptop","VPN","notebook"}&"*"))>0

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

 

[circuit_breaker]

Wow, I love this one Thanks.

Try this:
=SUMPRODUCT(--ISNUMBER(SEARCH({"Laptop","VPN","notebook"},A1)))>0

or this:
=SUM(COUNTIF(A1,"*"&{"Laptop","VPN","notebook"}&"*"))>0











- Show quoted text -

 

[Teethless mama]

You're Welcome!

Wow, I love this one Thanks.