Formula to See if a Range of Substrings is in a Particular cell and Where it begins...

[rjw524]

Hi,

I have a list of 500 terms in column A. (The sheet is called "TERMS")

On a separate spreadsheet in the same workbook, I have a list of single words. The list (let's say it's in column B) has 3000 rows.

(This sheet for simplicity is named "SUBSTRINGS)



I need a formula let's say column C on the "TERMS" sheet that will check EACH of the 500 cells in column TERMS" to determine if ANY of the 3000 words in the worksheet "SUBSTRING" is somewhere in the string.


So, Col A of Worksheet TERMS contains:

A1 = John sells lemonade for $1 per glass
A2 = Carla loves her dogs
A3 = Superman leaps tall buildings in a single bound


Let's Col B of worksheet SUBSTRINGS contains:

Superman
dogs
computers
cats
Batman
John
leaps
likes


I need a formula or formulas that:

1) Returns whether or not a word from SUBSTRING is in the particular cell of TERMS
and
2) Returns WHERE the substring begins.

I would like to do this using a formula (or 2 if necessary) but if a Macro is the way to go due to the number of calculations, I would of course be open to using that as well.

Any help would be greatly appreciated!

Thanks!

R.J.

[rjw524]

I actually figured this one out.

I listed it below in case anyone else ever needs a formula like this:

"=INDEX('SUBSTRINGS'!$A$1:$A$3000,MATCH(TRUE,ISNUMBER(SEARCH('SUBSTRINGS'!$A$1:$A$3000,'TERMS'!$B3)),0))"

R.J.

[AlKey]

You may also use this formula to lookup and return key-words. Also, you may get better results if sort your lookup list in ascending order.

=IF(ROWS(A$2:A2)>COUNTA(A:A)-1,"",LOOKUP(10^308,SEARCH($C$2:$C$9,A2),$C$2:$C$9))

Row\Col	A	B	C
1	Original string	Returned values	Key-words
2	John sells lemonade for $1 per glass	John	Batman
3	Carla loves her dogs	dog	cat
4	Superman leaps tall buildings in a single bound	Superman	computers
5			dog
6			John
7			leap
8			like
9			Superman

[rjw524]

Hi AllKey,

I actually like this formula a lot. It looks much cleaner.

I'm having a little trouble following the logic of it however.

Would it be asking too much if you could give a quick explanation of what this formula is doing exactly? I'd love to understand it better.

Thanks!

R.J.

[AlKey]

Hi AllKey,

I actually like this formula a lot. It looks much cleaner.

I'm having a little trouble following the logic of it however.

Would it be asking too much if you could give a quick explanation of what this formula is doing exactly? I'd love to understand it better.

Thanks!

R.J.

The first part =IF(ROWS(A$2:A2)>COUNTA(A:A)-1,"" is there to stop formula to prevent error when there is no more rows of data in column A. Since you still using Excel 2003, as it shows in your profile, there other option would be to use "IF(ISERROR(your formula,"", your formula)" but that would also make the formula much longer. There are better options for those who use Excel 2007 and higher.

The second part is LOOKUP and SEARCH combination:

The SEARCH($C$2:$C$9,A2),$C$2:$C$9)

Here formula will look in each cell starting with A2, to find key-words located in column C and if any of the words found it will return the first matched word from the same column C. But the key to all this is the LOOKUP function: LOOKUP(10^308 and 10^308

10^308 is an enormously large number that can be entered in Excel and most likely doesn't exist withing the lookup_vector. Since the LOOKUP function typically works on a sorted (ascending order) range it will go for the last row assuming that this where the largest value exists. However, when used with SEARCH or FIND functions, the only number it will find is the one returned by search function.