Share via

Find keywords in cell - what is best, vlookup, lookup, search, index?

Anonymous
2012-12-03T17:05:35+00:00

I've seen many posts about this, but nothing specific to my concern.

I get a spread sheet every other day that I normally use the inline pop up search to filter and find rows of records that match keywords. This works fine as long as I don't have many keywords, but over the past year the keyword list has grown to almost 100. This has gotten out of hand. Is their a formula or macro I could use to replicate this, or create a macro, which I've never done before.

What I have done is created a separate spread sheet with the keywords, and a second column to with a note of the possible action I'd be looking to do for that record. Then added a new column in original spreadsheet to do a vlookup and return the possible action second column. This kinda works, but misses a lot of records. I've added Wildcards, "*"&Cell&"*" but this still isn't optimal. I can't control where in the string of text the keywords will be. It's always random.

The weird thing is I basically just want to do what Excel does when you use text search which is pretty powerful, but I want to do all the words at one time verses over and over again.

Here's the actual formula: =VLOOKUP("*"&A2&"*",'[TitleLookup.xlsx]Sheet1'!$A$2:$B$100,2,FALSE)

Spreadsheet A - The data I want to search

Columns A2-A5000 would have this type of random data

Dog Catcher

Full Time Student

Unemployed

student

employee

Spreadsheet B - The Keyword Array

Columns A2:B35 has the keyword and action description to flag the record by

A2 student, B2, Student

employed, Confirm Employment

employee, Confirm Employment

unemployed, Confirm Qualifications

I found a few examples but most assume the keyword will be in a static place, and mine are not. My goals is just to flag these records and provide a hint of what i'm supposed to look for on that record.

My next step is to see if MS Access might be what I should be moving too, but I just don't think its right for my needs. I do so much sorting and filtering and Excel is so much quicker (or at least with my skill level in both programs) than when I try to do this in Access.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2012-12-03T18:22:25+00:00

Due to the fact that you are attempting to search any number of individual words within a column of many keywords you will require some form of loop to cycle through the possibilities. Further, wildcards can cause some problems as illustrated by your sample data where employed will match both Employed and Unemployed. I would suggest a custom User Defined Function (i.e. UDF) that has been written for your specific situation.

Tap Alt+F11 and when the VBE opens immediately use the pull-down menus to Insert, Module Paste the following into the new pane titled something like Book1 - Module1 (Code), 

Public Function udfFindAction(rFnd As Range, rKyWrds As Range) As String

Dim sTmp As String, vWrd As Variant, vWrds As Variant

vWrds = Split(rFnd, Chr(32))

sTmp = vbNullString

For Each vWrd In vWrds

If Not IsError(Application.VLookup(vWrd, rKyWrds, 2, False)) Then

sTmp = Application.VLookup(vWrd, rKyWrds, 2, False)

Exit For

End If

Next vWrd

udfFindAction = sTmp

End Function

Tap Alt+Q to return to your worksheet. Use as follows:

=udfFindAction(A2,Sheet2!$A$2:$B$5)

... where A2 is the cell in Spreadsheet A that you want to search the individual words from and Sheet2!$A$2:$B$5 is the table of keywords and actions on Spreadsheet B. Once entered correctly in B2, fill down as necessary.

            

Was this answer helpful?

0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-05-24T15:35:39+00:00

    FORMULA GIVEN:

    ...  Tap Alt+F11 and when the VBE opens immediately use the pull-down menus to Insert, Module Paste the following into the new pane titled something like Book1 - Module1 (Code), 

    Public Function udfFindAction(rFnd As Range, rKyWrds As Range) As String

    Dim sTmp As String, vWrd As Variant, vWrds As Variant

    vWrds = Split(rFnd, Chr(32))

    sTmp = vbNullString

    For Each vWrd In vWrds

    If Not IsError(Application.VLookup(vWrd, rKyWrds, 2, False)) Then

    sTmp = Application.VLookup(vWrd, rKyWrds, 2, False)

    Exit For

    End If

    Next vWrd

    udfFindAction = sTmp

    End Function

    Tap Alt+Q to return to your worksheet. Use as follows:

    =udfFindAction(A2,Sheet2!$A$2:$B$5)

    ...

    QUESTION: 

    Hi Jeeped,

    Hopefully you are still monitoring ...

    I think this formula may work for me but this is definitely more advanced than I am. Love learning something new.

    Similar issue as Jose Malone as far as the need is concerned. But NOTHING seems to happen -- no returns???

    Just having a bit of difficulty getting the "results" for this formula and thought I might need an IF statement added somewhere, but again not sure. My final UDF formula is =udfFindAction(CB25,'no list'!A:B).

    I followed your instructions to paste the first formula/function in VBA module. Then pasted the =udf... formula into an empty cell in which I wanted a comment returned that would say "don't send" or something to that effect that indicates a name already exists in my keyword table.

    Procedure:

    When I pasted the =udf... formula, a menu opened directing me to locate the file name then sheet. Which was cool to see. After I located the sheet where my keywords are located (which is in the same workbook as the =udf formula but a different sheet), I got stuck.

    Nothing is returned to indicate duplicates or a common name or one keyword from the KEYWORD sheet. That's when I thought I might need to add an IF statement, but that is not your instructions to Jose, so I'm thinking I don't need an IF statement either. So I am stuck -- nothing has identified a keyword in my list, which I know exists.

    I do hope I have written this clearly. Thanks for any help/insight.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2012-12-03T19:07:47+00:00

    Jeeped,

    Excellent! Thank you, again!

    Jose

    Was this answer helpful?

    0 comments
  3. Anonymous
    2012-12-03T19:03:27+00:00

    Hi Jose,

    Thank you for the kind words and the feedback. If you want this UDF (and possible future custom routines) available to all future uses of Excel, you could blank out the data and save the workbook as an Excel Binary Workbook in the XLSTART folder. This allows access to macros and other custom routines that are used repetitively.

    Customize how Excel starts

    Another option is to create a workbook template from the current workbook that future keyword/action searches will be created from.

    Save a workbook or worksheet as a template - Excel - Office.com

    You might also want to explore the process of digitally signing the code modules as a Trusted Publisher to avoid the necessity of repeatedly confirming macro access.

    Digitally sign a macro project - Excel - Office.com

    Was this answer helpful?

    0 comments
  4. Anonymous
    2012-12-03T18:39:32+00:00

    Jeeped,

    OMG! This is awesome!!! Thank you so much! This will save me so many hours and repetitive disorders to come!

    For future reference. Is it possible to keep this UDF within Excel so its always available? Or will I have to create it each time for every spreadsheet? That's not the end of the world, but it'd be great to just have it always there.

    Sincerely,

    Jose

    Was this answer helpful?

    0 comments