Partial Match Lookup

[mikesc]

I have one worksheet with data in it. Named (data)
Top row with sample data is as follows:
Server, assigned , category, domain, ping


Data work sheet:


A B C D E
1 Server Assigned category domain ping
2 server1 Pending category 01 cab pass
3 server2 Pending Automatic/Scripted def pass
4 server3 Pending Automatic/Scripted def pass
5 test1 Pending category 03 def pass
6 test2 Pending Automatic/Scripted def pass
7 test3 Pending Automatic/Scripted cab pass
8 ttadexx01 Pending Automatic/Scripted cab pass
9 ttadexx02 Pending Automatic/Scripted cab pass
10 teadexx03 Pending Automatic/Scripted cab pass
11 dexx01 pending Automatic/Scripted xxx pass
12 dexx02 pending Automatic/Scripted xxx pass
13 dexx03 pending Automatic/Scripted xxx pass
14 xserv-01 pending Automatic/Scripted xxx pass

Formula in Column C for row 2 is as follows:

=IF(ISNA(VLOOKUP(A2,index!A:B,2,FALSE))=TRUE,"Automatic/Scripted",VLOOKUP(A2,index!A:B,2,FALSE))

The 'category' Column is used to define a server category type by referencing an index work sheet.

Index work sheet:



A B
1 category Name return value
2 Server1 category 01
3 Server category 01B
4 dex category 02
5 Test1 category 03
6 test category 03b


The Formula that I am using is not working 100% yet.
I'm trying to define a Category base off of a few key words or string of text (in the "A" Column in worksheet "index"), find any server name in the data sheet that would match it.
For example: just like how the data> filter works.

If you type in a word or a text it will search that Column and find all matches and only show these matches.
What I'm trying to do with this Formula, is if it's not able to find any matches. Then it should return the default value of "Automatic/Scripted"


Right now it’s only able to find exact matches. The only Server that should have the default value of “Automatic/Scripted” is “xserv-01” because it is not defined in the index worksheet.

Can someone please help me tweak this Formula?