Searching if a cell contains a range of values

**madasu** · 07-03-2014, 08:15 PM

Hi All,

So I use this formula to check if a cell contains a specified piece of text in the case it is "SP" and the return a result

IF(ISNUMBER(SEARCH("sp",A1)),"EBC", "ONLINE")

What I need to do now is check to see is a cell contains a value that is in a range of values.

For example.

in A1 I would have the value xyz.com

In my range of have thousands of values ) one of which is xyz (not xyz.com) and returns a positive or negative result

How do I create a formula that

Cheers

LUke

**martindwilson** · 07-03-2014, 08:40 PM

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**madasu** · 07-03-2014, 08:47 PM

Thanks

The named range id called "gtld"example.xlsx

**martindwilson** · 07-03-2014, 09:03 PM

try in B2 of sheet2 filled down something like,change true/false results as you need
=IF(ISNUMBER(LOOKUP(10^99,SEARCH(gtld,A2))),"found","not found")

**madasu** · 07-03-2014, 09:05 PM

OMG you are a legend thank you

**madasu** · 07-03-2014, 10:32 PM

Sorry one more question. What if I wanted to return a result from found result in that range?

**Psyphon** · 07-04-2014, 12:08 AM

Originally Posted by martindwilson

try in B2 of sheet2 filled down something like,change true/false results as you need
=IF(ISNUMBER(LOOKUP(10^99,SEARCH(gtld,A2))),"found","not found")

This is an excellent method! I've been trying to figure out the best way to do this, as well.
Would you know how to apply this to a search through two columns of data? Essentially, using the same range of data to check against two columns, instead of one.

I've tried inserting the OR function, but I keep getting a "too many arguments" error. I've even tried parsing the ISNUMBER(LOOKUP(... section from named cells on my Table sheet. No luck, so far.

**martindwilson** · 07-04-2014, 04:12 AM

Psyphon welcome to the forum but>>>>>>>
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

**martindwilson** · 07-04-2014, 04:39 AM

ah i forgot a bit ,that formula would say found if it found the x in xaa ,trouble is search will find anything in the string that matches so loads of partial matches may be found
to limit it you can sort the data to be looked up by length ,smallest to largest then the longest match will be returned