vLookup with wildcards (equivalent of "contains") - best solution?

FlossieT

New member
Joined
Jul 12, 2011
Messages
11
Reaction score
0
Points
0
I'm doing some work on analysing website search terms. I have:

- one worksheet, search_terms, containing all the search terms for a given month
- one worksheet, author_names, containing a list of author names.

I want to add a column to search_terms that sets a flag whenever a name from author_names has been used in search_terms. However, I can only seem to get this working - with vLookup - for an exact match. It's not flagging rows in which, for example, an author name appears along with a subject keyword. So, "Joe Bloggs" returns a 1; "Joe Bloggs cheese" doesn't.

Is there a way to do this, either using vLookup or using something else? I've tried a couple of approaches using wildcards, but I can only seem to get it to work in the other direction (i.e. when the data in search_terms is a subset of the data in author_names).

Many thanks,

Rachael
 

FlossieT

New member
Joined
Jul 12, 2011
Messages
11
Reaction score
0
Points
0
I've tried that, but it doesn't seem to be working. E.g. I have an entry "joanne bloggs, operation" in search_terms, and "Joanne Bloggs" in author_names, but the vLookup isn't matching those two.

Does it have to match case exactly?

I'm using Excel 2011 on Mac OSX 10.6.
 

FlossieT

New member
Joined
Jul 12, 2011
Messages
11
Reaction score
0
Points
0
I've just tried lowercasing all the author names in the author_names worksheet, and it still doesn't work - matching fine when the author name is the only thing in the cell, not when there's any other text involved.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hi there, and welcome to the forum!

I'm curious, are you trying to look up "Joanne Bloggs" in a table containing "joanne blogs, operation", or trying to look up "joanne bloggs, operation" in a table containing "Joanne Bloggs"?
 

FlossieT

New member
Joined
Jul 12, 2011
Messages
11
Reaction score
0
Points
0
The first - sorry if that wasn't clear from the OP.

I have one sheet with a list of author names, and one with a list of search terms which may or may not include the author names. I want to flag the ones that do, but the vLookup is only finding exact matches.

I suppose I could turn it the other way round and look up my search terms in the author names table, see if that will find the matches, but it's a larger table, and I don't really want to sort the search terms alphabetically (I guess I could do it temporarily).
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
No, don't turn it around. That's would make it MUCH harder. To look up the author name in the search terms table should be a piece of cake.

Have a look at the attached file and see if that does what you're after.
 

Attachments

  • xlgf270-1.xlsx
    10.1 KB · Views: 10,523

FlossieT

New member
Joined
Jul 12, 2011
Messages
11
Reaction score
0
Points
0
This is working, but the information is the wrong way round (so the "MUCH harder" you refer to...) compared to how I have it set up. My 'table' is the author names and the lookup terms are the search terms.

So it sounds like I have it back to front...
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Yes... the issue is that when you search using vlookup, it needs to find the whole string you pass to the function. So the only route you really have here is to work up some formulas to trim down the parameter that you're passing it. Out of curiosity, are your search terms always Author first, followed by a comma, then by more terms? If so you could write a formula to only take the characters up to the first comma...
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Here, try this one. You should be able to search for "Joanne Bloggs, Operation" or "Joanne Bloggs" and return a result. The key is that the author name must be first in the query string though.
 

Attachments

  • xlgf270-2.xlsx
    10.1 KB · Views: 8,107

FlossieT

New member
Joined
Jul 12, 2011
Messages
11
Reaction score
0
Points
0
Could be in any order - these are search terms 'in the wild', so could be ANYTHING! I just want to find the ones that refer to actual authors, not generic subject-related terms.

Edit: also compared to your example, there may be many, many entries for each author, and I'd want to find all of them.

Is it possible to do this with a different formula?
 
Last edited:

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hmmm... In that case, to be honest, I'd probably do one of the following:
1) Flip the logic so that you're looking up authors against your list of searc terms, or
2) Write VBA code to check for matches

If you can do the first, I would. The second is going to take more time and it's less likely to be maintainable depending on the skillsets of the users involved...
 

FlossieT

New member
Joined
Jul 12, 2011
Messages
11
Reaction score
0
Points
0
I'll have a go at flipping, but I'm not sure I can quite get my head around it... thanks for all your help, though - very much appreciated. Don't want to go down the VBA route if I can help it!!
 

FlossieT

New member
Joined
Jul 12, 2011
Messages
11
Reaction score
0
Points
0
The problem is, I need to return multiple results for each author name, and I also need to record those results against the search terms for further analysis. I don't think this is going to work...
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
You already have a table of authors, correct? Just run the formula down the side of that table instead of the search terms and I'd think you'd be good. (If you feel you can share your data, you could always upload your workbook so we could help work on it.)
 

FlossieT

New member
Joined
Jul 12, 2011
Messages
11
Reaction score
0
Points
0
I can't share the original data, unfortunately, but I'll try and put something together that shows the problem... give me a minute!
 

FlossieT

New member
Joined
Jul 12, 2011
Messages
11
Reaction score
0
Points
0
OK - in case it helps, I've attached a mockup of what I'm trying to do (with a very small dataset - the one I'm actually working with is several thousand rows); the 'desired result' tab shows what should be happening with the author flagging (but I can't get to work...).

View attachment author_names.xlsx
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
I'm not going to say that this can't be done with a formula, but I haven't been successful in working it out. I can do it with VBA though using the following UDF:

Code:
Function COUNTLIKE(arg As Range, pattern As String) As Long
    Dim cl As Range
    For Each cl In arg
        If LCase(pattern) Like "*" & LCase(cl.Value) & "*" Then COUNTLIKE = COUNTLIKE + 1
    Next cl
End Function

I've attached a copy of the workbook with the UDF in use. If you'd like to go with it, let me know and I can help you out with the steps to put it in place.
 

Attachments

  • author_names.xlsm
    17.6 KB · Views: 256

Simon Lloyd

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
Excel 2016
I'm not going to say that this can't be done with a formula, but I haven't been successful in working it out. I can do it with VBA though using the following UDF:

Code:
Function COUNTLIKE(arg As Range, pattern As String) As Long
    Dim cl As Range
    For Each cl In arg
        If LCase(pattern) Like "*" & LCase(cl.Value) & "*" Then COUNTLIKE = COUNTLIKE + 1
    Next cl
End Function

I've attached a copy of the workbook with the UDF in use. If you'd like to go with it, let me know and I can help you out with the steps to put it in place.
Bob may disagree Ken, give him a nudge on this one as he loves a challenge, in fact i believe he created a formula for me on a similar subject but using sumproduct =SUMPRODUCT(--(LEFT(Sheet1!$A$1:$A$10&" ",FIND(" ",A1&" "))=LEFT(A1&" ",FIND(" ",A1&" "))),Sheet1!$B$1:$B$10) you may find this thread interesting http://www.vbaexpress.com/forum/showthread.php?t=33410
 
Top