Key Word Search

Big Leeroy

New Member
Joined
Oct 14, 2006
Messages
6
Hello,

Is there a function/formula that would allow me to perform a keyword search on a work sheet.

I currently have a list of book titles in column a, and key words in column b. I would like to be able to enter a word in e1 and return a value of a book title in e2.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,437
Office Version
  1. 365
Platform
  1. Windows
Big Leeroy

Welcome to the Mr Excel board!

A little clarification please:
1. Does each cell in column B contain just one keyword, or several?
2. Can you confirm, for example, that the keyword(s) in say B5 correspond to the book title in A5?
3. What do you want to happen if the keyword in E1 occurs several times in column B?
 

Big Leeroy

New Member
Joined
Oct 14, 2006
Messages
6
Thank you Peter_SSs for assisting me.

Each cell in Columm B contains multiple keywords.
Example: Psycology, Child, School

The key words in Columm B all correspond to there associated book titles in Columm A. Example: A5's title has the keywords located in B5

I would like a search to return the title of the book that has matches for key words, If multiple titles match, I would like it to list the multiple matches. Not to sure if that is possable.

Again thankyou for all your help Peter_SSs and anyone else that helps.
 

larrydunn

Board Regular
Joined
Jun 1, 2003
Messages
130
Here is code that will list matching titles starting at cell F1. It is good if there is only one keyword in cell E1.

The process probably could be later refined by using Data Validation at E1, and possibly a Worksheet Change event to trigger the lookup code automatically when E1 is changed.

But one thing at a time!

Sub MatchSearchWord()
'List matching book titles for search word in E1
Dim sSearchWord As String
Dim sBookTitles() As String
Dim IBookCount As Integer
Dim I As Integer

ReDim sBookTitles(2)

sSearchWord = ActiveSheet.Range("E1").Value

'Go to top of book names column
ActiveSheet.Range("A1").Select

ReadAllTitles:
If ActiveCell.Value = "" Then GoTo GotAllTitles 'Assume no blanks in this column
IBookCount = IBookCount + 1
If IBookCount <= UBound(sBookTitles) Then GoTo PutInArray
ReDim Preserve sBookTitles(IBookCount + 10)

PutInArray:
sBookTitles(IBookCount) = ActiveCell.Value
ActiveCell.Offset(1, 0).Range("A1").Select 'Move down
GoTo ReadAllTitles

GotAllTitles:

'List all titles, starting at F1
ActiveSheet.Range("F1").Select

For I = 1 To IBookCount
If InStr(UCase(sBookTitles(I)), UCase(sSearchWord)) < 1 Then GoTo SkipBook
ActiveCell.Value = sBookTitles(I)
ActiveCell.Offset(1, 0).Range("A1").Select 'Move down
SkipBook:
Next I

End Sub
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi

Here is a simple solutions with an auxilliary column.

In C2:

Code:
=IF(ISNUMBER(SEARCH($E$2,B2)),MAX($C$1:C1)+1,"")
Copy down

In F2:

Code:
=IF(ROWS($F$2:F2)>MAX(C:C),"",INDEX(A:A,MATCH(ROWS($F$2:F2),C:C,0)))
Copy down

You can hide column C.

Hope this helps
PGC
SearchByKey.xls
ABCDEFG
1TitleKeywordsKeywordTitles
2Book1Children,Mathematics1matBook1
3Book2Philosophy Book3
4Book3School,Mathematics,Children2Book5
5Book4Gastronomy Book8
6Book5Mathematics3Book10
7Book6Children  
8Book7School,Philosophy  
9Book8School,Mathematics,Children4 
10Book9Astronomy  
11Book10Children,Mathematics5 
12
13
Sheet4
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,437
Office Version
  1. 365
Platform
  1. Windows
Here is a similar approach to pgc01 but with a few less function calls.

1. C2 (copied down): =COUNTIF(B$2:B2,"*"&E$2&"*")
2. E5: =MAX(C:C)
3. F2 (copied down): =IF(ROWS(F$2:F2)>$E$5,"",INDEX(A:A,MATCH(ROWS(F$2:F2),C:C,0)))
Mr Excel.xls
ABCDEFG
1TitleKeywordsKeywordTitles
2Book1Children, Mathematics1matBook1
3Book2Philosophy1Book3
4Book3School, Mathematics,Children2No. MatchesBook5
5Book4Gastronomy25Book8
6Book5Mathematics3Book10
7Book6Children3 
8Book7School, Philosophy3 
9Book8School, Mathematics,Children4 
10Book9Astronomy4 
11Book10Children, Mathematics5
12
Keyword Search
 

Forum statistics

Threads
1,136,520
Messages
5,676,340
Members
419,619
Latest member
jalme

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top