How can I do a partial match in a vlookup (True fails)

[bdmeyer]

I have a spreadsheet with thousands of lines from event logs.

I want to search for very specific wors int he event logs.

Here are a couple example lines from an event log:
Name: Microsoft-Windows-DiskDiagnosticDataCollector Status: SCHED_S_TASK_DISABLED MD5: [N/A] Creator: Microsoft Corporation
Message: Virus/spyware 'Troj/SWFExp-CA-++-dsamjnk[dot]xxxxxxxx[dot]com/xxxxxxx/69fcdebf2416ea55454e0058565...-++-dsamjnk.xxxxxxxxxxxxxx[dot]com/xxxxxxxxx/69fcdebf2416ea55454e0058565e575f060b51585007525d030a055653005a03;119900;117-++-

In another worksheet in that workbook, I have this lookup:
=IF(ISERROR(VLOOKUP(G4,Search_lists!D:D,1,FALSE)),"No","Yes")
For testing, I copy one of the two lines above int he lookup table, and it works perfectly.

I need to make this a bit more generic.
What my end goal is, without breaking the other functionality, is to match on snippets that I place into the lookup table.
So using the example above, I want to match on:

Virus
spyware
Torj
SWFExp
(any single one not necessarily a combo)

I tried replacing False with True but that matches on anything including stuff that isn't even in the field I am searching on (Event log data)
I've tried using the terms above with wildcards in them, but that doesn't work:

*Virus*
*spyware*
*Troj*
*SWFExp*

Any guidance would really be appreciated.

Thank you.

 

[bdmeyer]

I worked on this some more last night.

This time I tried to approach it from the MATCH side.

To just get the syntax working, Here is what I tried:


Cell G4:
[TABLE="width: 766"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Name: Scheduled Status: SCHED_S_TASK_READY MD5: [N/A] Creator: Microsoft Corporation
[/TD]
[/TR]
</tbody>[/TABLE]
Item in lookup table that should be a match:
MD5:
My adaptation of the offered code above:
=IF(ISERROR(MATCH("*"&G4&"*",Search_lists!D:D,0))=FALSE,"Yes","No")

That fails.
When I replace the string in G4 with one word (Virus) it matches.
If I change it to:
Virus Hi (in G4) it fails
This makes me think the wild carding is not working for some reason.

 

[Andrew Poulsom]

That would indicate that:

Name: Scheduled Status: SCHED_S_TASK_READY MD5: [N/A] Creator: Microsoft Corporation

doesn't appear anywhere within any of the text in column D on worksheet Search_lists. The formula works for me:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Name: Microsoft-Windows-DiskDiagnosticDataCollector Status: SCHED_S_TASK_DISABLED MD5: [N/A] Creator: Microsoft Corporation
2	Some random text Name: Scheduled Status: SCHED_S_TASK_READY MD5: [N/A] Creator: Microsoft Corporation Some random text
3
4							Name: Scheduled Status: SCHED_S_TASK_READY MD5: [N/A] Creator: Microsoft Corporation
5							Yes
Sheet1

Cell Formulas
Range	Formula
G5	=IF(ISERROR(MATCH("*"&G4&"*",A:A,0))=FALSE,"Yes","No")

 

[bdmeyer]

This post below overlapped yours. I'll leave it, but need to post a sample spreadsheet as I don't think my words are describing it correctly.

After reading this:
[TABLE="class: collapse"]
<tbody>[TR="class: trbgeven"]
[TH]Match_type[/TH]
[TH]Behavior[/TH]
[/TR]
[TR="class: trbgodd"]
[TD]1 or omitted[/TD]
[TD]MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.[/TD]
[/TR]
[TR="class: trbgeven"]
[TD]0[/TD]
[TD]MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order.[/TD]
[/TR]
[TR="class: trbgodd"]
[TD]-1[/TD]
[TD]MATCH finds the smallest value that is greater than or equal to lookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.[/TD]
[/TR]
</tbody>[/TABLE]


I suspect this just won't work for what I am trying to do. Ultimately, I want to be able to use the IFERROR function (or anything similar) as in my example way above to either set a YES or NO value that I'll use in filtering.

 

[bdmeyer]

Ignore this, I confused myself, I'll repost in a minute

 

[bdmeyer]

=IF(ISERROR(MATCH("*"&G4&"*",A:A,0))=FALSE,"Yes","No")

It almost looks backwards.


Column G has one string per row like:
G1| random
G2| fubars
G3| trojan
G4| virus



When we search through row A1 to see if any of the strings in column G exist, If any of them do, then 'Yes' if not, then 'No'
A1 has no match
When we run on A2, it would match on 'random', so then 'Yes'

 

[Andrew Poulsom]

Is this what you want?

Excel 2010
	A	B	C	D	E	F	G
1	Some random text Name: Scheduled Status: SCHED_S_TASK_READY MD5: [N/A] Creator: Microsoft Corporation Some random text	Yes					random
2	Some other text	No					fubars
3							trojan
4							virus
Sheet1

Cell Formulas
Range	Formula
B1	=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(G$1:G$4,A1)))),"Yes","No")

 

[bdmeyer]

It certainly looks correct. My conversion didn't work, but that is almost certainly me. I'll post when I have the chance to pound on it some more.
Thank you for your help.

 

[bdmeyer]

Short of running it on live data, I think it works correctly. I have it working on my sample data. Had to sort the lookup table it references which was the final problem.
Looks like I can't use a named range for some reason, I'll have to research that.

Thank you for all your help. Learned quite a bit on this one.
-= Bruce D. Meyer