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.

 

[Andrew Poulsom]

Welcome to MrExcel.

MATCH will accept wildcards, but it appears that the text length in the lookup range is limited to 255 characters and your sample is 350.

 

[Jonmo1]

Welcome to the board.

The wildcard should work
VLOOKUP("*Virus*",Search_lists!D:D,1,FALSE)
or with a cell reference G4 = Virus
VLOOKUP("*"&G4&"*",Search_lists!D:D,1,FALSE)

 

[Andrew Poulsom]

Welcome to the board.

The wildcard should work
VLOOKUP("*Virus*",Search_lists!D:D,1,FALSE)
or with a cell reference G4 = Virus
VLOOKUP("*"&G4&"*",Search_lists!D:D,1,FALSE)

From testing it won't return a value greater than 255 characters.

 

[Jonmo1]

From testing it won't return a value greater than 255 characters.

I'm not so sure that the text is over 255 characters..
I don't think the example given is all in one cell.

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-++-

Plus given this statement..

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.

 

[bdmeyer]

Welcome to MrExcel.

MATCH will accept wildcards, but it appears that the text length in the lookup range is limited to 255 characters and your sample is 350.

Thank-you!

I had actually shortened that by about 100 chars also.
Rats. I wonder if it onsly works on the first 255, or anything over 255 breaks it.
I'll look up MATCH. If nothing else works, it will be better than nothing.

Thank you for your help Sir.

 

[bdmeyer]

True. I am not using MATCH though.
I tried the modified:
=IF(ISERROR(VLOOKUP("*"&G4&"*",Search_lists!D:D,1,FALSE)),"No","Yes")
and it didn't match.

I am going over it some more to make sure I didn't mess something up adding your vlookup into my IF(ERROR...

 

[Andrew Poulsom]

I'm not so sure that the text is over 255 characters..

Agreed.

 

[bdmeyer]

Agreed.

Yes, it is one cell. 234 chars.

 

[bdmeyer]

Yes, it is one cell. 234 chars.

Let me re-phrase that. They are two log entries. The second one, is 234 chars.