Keyword searching

  • Thread starter Thread starter Geoff Sale
  • Start date Start date
G

Geoff Sale

I'm trying to use Excel for a task probably best suited for a database:
keyword search and hit tally from lines of text.
I've tried SEARCH, FIND, MATCH, H&VLOOKUP etc in various combinations,
but so far no luck. I only ever see what's in the matching row, even
though I've defined my sentence list as an array and do my searches in
the array.
Eg.:
Data sentences in A:A:

A1: the fox jumped over the lazy dog
A2: hens have no teeth
..
..
An-1 cows sleep standing up
An brown eggs come from brown chickens

Keywords, formula-based extracts from other sentence statements:

B1: fox C1: dog D1: chickens E1: eggs
B2: cat C2: pig D2: goats E2: hay

All I need is an indication of the existence of any of the B1:Ex
keywords in any of the A:A sentences.
Should I be using a Pivot table or something like that?
I have several hundred data sentences and probably 200 keywords.

Thanks for any help!
Geoff
 
Geoff,

If I understand correctly, I think I have a solution to
your problem. I did a simplified version of your keyword
search, and I got the right answer. To make it easier for
me to count, I entered the following in cells A1:A5

a b c d e f
a b c d e f
a b c d e f
g h I j k l
g h I j k l

Then in cell C1 I entered A and D1 entered k. In cell C2
I array-entered (hit CTRL-SHIFT-ENTER after typing the
formula) the following:

=COUNT(SEARCH($C$1:$D$1,$A$1:$A$5))

It seems to work for me!

HTH

Eric
 
Back
Top