PDA

View Full Version : vLookup with wildcards (equivalent of "contains") - best solution?



FlossieT
2011-07-12, 01:28 PM
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

Simon Lloyd
2011-07-12, 03:59 PM
You can use wildcards like this:
=VLOOKUP("*"&C1&"*",author_names!A1:B100,1,FALSE)

FlossieT
2011-07-12, 04:21 PM
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
2011-07-12, 04:42 PM
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
2011-07-12, 05:05 PM
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
2011-07-12, 05:10 PM
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
2011-07-12, 05:14 PM
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.

FlossieT
2011-07-12, 05:22 PM
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
2011-07-12, 05:29 PM
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
2011-07-12, 05:34 PM
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.

FlossieT
2011-07-12, 05:35 PM
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?

Ken Puls
2011-07-12, 05:40 PM
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
2011-07-12, 05:44 PM
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
2011-07-12, 05:47 PM
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
2011-07-12, 05:49 PM
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
2011-07-12, 05:53 PM
I can't share the original data, unfortunately, but I'll try and put something together that shows the problem... give me a minute!

Ken Puls
2011-07-12, 05:57 PM
I'm going to give this one some thought today and will loop back on it later. :)

FlossieT
2011-07-12, 06:03 PM
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...).

150

Ken Puls
2011-07-13, 07:14 AM
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:


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.

Simon Lloyd
2011-07-13, 04:14 PM
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:


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

Ken Puls
2011-07-13, 04:47 PM
I don't think Bob would disagree, Simon. I didn't say it couldn't be done, just that I couldn't figure it out. ;) (I fully expect Bob would shake his head at me and bang it off without even needing to test it.)

Bob Phillips
2011-07-13, 06:16 PM
I think this ARRAY formula does what you want

=--SUM(IFERROR(MATCH("*"&author_names!$A$2:$A$6&"*",A2,0),0))

Ken Puls
2011-07-13, 06:22 PM
Thanks Bob. I dropped it in and Excel corrected it (I think the board picked up an extra space in there between the A and 2.)

Flossie, to commit the Array formula, enter the formula then press CTRL+SHFT+ENTER. Then copy it down to the other cells.

Bob, one day I need you to write a book on working with array formulae. ;)

Bob Phillips
2011-07-13, 07:06 PM
Bob, one day I need you to write a book on working with array formulae. ;)

Ken, As you well know, there is no money and little recognition in writing a book.

Ken Puls
2011-07-13, 11:13 PM
Ken, As you well know, there is no money and little recognition in writing a book.

LOL! True enough. :)

Simon Lloyd
2011-07-14, 09:52 PM
Besides which Ken Bob IS the book :), you know, i wish fishing was as easy as getting Bob to answer a formula question, just throw him the "I don't think it can be done..." and it's like a red rag to a bull ;)

Simon Lloyd
2011-07-14, 09:57 PM
Hi Bob, why the coercion with the double unary?

Ken Puls
2011-07-14, 10:38 PM
"I don't think it can be done..."
LOL! EVERY time I say this someone comes along and proves otherwise. ;)

Bob Phillips
2011-07-15, 12:13 AM
Hi Bob, why the coercion with the double unary? Do you know, I was wondering that myself. It is certainly not necessary, probably I ised it in s first attempt and didn't remove it.

Simon Lloyd
2011-07-15, 12:21 AM
......probably I ised it in s first attempt and didn't remove it.Nice to see you are human after all and that it sometimes does take you more than one go ;)

Actually i thought it was a "best kept secret" of manipulating SUM, so i thought there was going to pearls cast before me :D

FlossieT
2011-07-20, 01:23 PM
Thanks, all, for this, and apologies for delay in responding - a combination of out of the office and then not being able to post on the board...

This works 'in the wild' for me. The only thing is that instead of just a 1 or a 0, I get numbers through to 6. Is the formula also counting the total number of occurrences, or is there something else going on here?

Bob Phillips
2011-07-20, 01:37 PM
This could happen if you have multiples. Try this array version instead

=--(SUM(IFERROR(MATCH("*"&author_names!$A$2:$A$7&"*",A2,0),0))>0)

(Simon, I recall the -- now, this is what I originally tried)

Bob Phillips
2011-07-20, 01:38 PM
You could also use

=MAX(IFERROR(MATCH("*"&author_names!$A$2:$A$7&"*",$A2,0),0))

Bob Phillips
2011-07-21, 09:13 AM
Just for the hell of it, a non-array, SUMPRODUCT solution

=--(SUMPRODUCT(--(ISNUMBER(MATCH("*"&author_names!$A$2:$A$7&"*",$A2,0))))>0)

Simon Lloyd
2011-07-21, 10:15 AM
Just for the hell of it, a non-array, SUMPRODUCT solution

=--(SUMPRODUCT(--(ISNUMBER(MATCH("*"&author_names!$A$2:$A$7&"*",$A2,0))))>0)And there we have it :), knew you couldn't resist, some may call it predictable, i choose to call it dependable ;)

LJM
2013-11-08, 09:53 AM
And there we have it :), knew you couldn't resist, some may call it predictable, i choose to call it dependable ;)

I've been hunting around for a solution for sometime now and stumbled across this forum.

Just signed up to say thank you and I shall be using this website some more when I need a solution!

Liam

chart68
2015-01-13, 12:01 PM
Just for the hell of it, a non-array, SUMPRODUCT solution

=--(SUMPRODUCT(--(ISNUMBER(MATCH("*"&author_names!$A$2:$A$7&"*",$A2,0))))>0)

Hello I've just found this & I love it, it's worked out my match, but I once I've matched the value in col A I want to output the value in column B, any help would be extremely appreciated.

Bob Phillips
2015-01-13, 01:49 PM
Just put the formula in column B?

chart68
2015-01-13, 03:14 PM
Just put the formula in column B?

Sorry I didn't make myself very clear, I have column A & B in sheet 2 and I need to look up the value of column B from that sheet 2 which matches the value in column A from the sheet 1.

I'm looking up from

=VLOOKUP("*"&A2&"*",Sheet2!$A$2:$B$100,2,FALSE) this won't work as the wildcard doesn't seem to match the data as per =--(SUMPRODUCT(--(ISNUMBER(MATCH("*"&Sheet2!$A$2:$A$100&"*",$A2,0))))>0) how do I get these to work together.

Thanks

Claire

Bob Phillips
2015-01-13, 03:46 PM
Claire, that VLOOKUP should work if I understand the data correctly. Give an example where it doesn't work.

chart68
2015-01-13, 04:13 PM
Claire, that VLOOKUP should work if I understand the data correctly. Give an example where it doesn't work.

I've uploaded my spreadsheet, thanks Claire

Bob Phillips
2015-01-14, 09:13 AM
You have a real problem there Claire. There is no realistic way of determining how those addresses map onto those areas. You need some sort of intermediary mapping table that can do that lookup.

chart68
2015-01-14, 09:28 AM
You have a real problem there Claire. There is no realistic way of determining how those addresses map onto those areas. You need some sort of intermediary mapping table that can do that lookup.

Thanks Bob, never mind it was worth a try :( your help and expertise is most appreciated. Claire

Mahendra
2015-06-24, 12:04 PM
I have two data one subscriber ID and Campaign Name. Campaign name contains subscriber ID.

Now looking for Campaign Name in in front of contains subscriber ID.

Please help to solve this...

Jeremy Fisher
2016-06-28, 06:20 PM
I think this ARRAY formula does what you want

=--SUM(IFERROR(MATCH("*"&author_names!$A$2:$A$6&"*",A2,0),0))

I've tried adding this formula to the spreadsheet given5219 but I can't get it to work. Does it work in excel 2003? If so it may be that I'm not getting the array to work properly.

Would someone mind posting the final working excel file here for me so I can see how it looks when working or suggest where I'm going wrong in trying to get this formula into the spreadsheet.

Jeremy Fisher
2016-06-28, 06:40 PM
I've tried adding this formula to the spreadsheet given5219 but I can't get it to work. Does it work in excel 2003? If so it may be that I'm not getting the array to work properly.

Would someone mind posting the final working excel file here for me so I can see how it looks when working or suggest where I'm going wrong in trying to get this formula into the spreadsheet.

Here's my attempt to do this which doesn't work for me.5220

Jeremy Fisher
2016-06-28, 06:58 PM
OK I got it to work with a different formula which I'll post here in case anyone is looking for a working file like I was: 5221