Use of wildcards inside MATCH

[JesusPM]

Hi experts,
I have a question regarding the use of INDEX,MATCH with a vector array and wildcards.
This example works fine, and it takes a partial string of 2-characters from A2 to return th ecorresponding device.

=INDEX({"Router","Meraki Router","Switch","Firewall","TermServer","Voice Gateway","TermServer"}, MATCH(MID(A2,6,2),{"rt","vp","sw","fw","ts","vg","-o"},0))

The problem that I have is that the nodes with the string "rt10[1-2]" are real routers, but the ones with string "rt1[1-2]" are switches (highlighted ones).
I've tried to deal with this by using this function, but it is not working with the wildcards:

=INDEX({"Router","Meraki Router","Switch","Switch","Firewall","TermServer","Voice Gateway","TermServer"}, MATCH(MID(A2,6,4),{"rt10","vp*","rt1*","sw*","fw*","ts*","vg*","-o*"},0))


I may be using the wildcards in the wrong way, or is this not supported?

 

[RoryA]

Wildcards only work for the lookup value, not the array you are looking up in, so you'd need to reverse the logic. See if this does what you want:

=LOOKUP(2,1/SEARCH({"vp","rt1","rt10","sw","fw","ts","vg","-o"},MID(A2,6,4)),{"Meraki Router","Switch","Router","Switch","Firewall","TermServer","Voice Gateway","TermServer"})

 

[JesusPM]

Wildcards only work for the lookup value, not the array you are looking up in, so you'd need to reverse the logic. See if this does what you want:

=LOOKUP(2,1/SEARCH({"vp","rt1","rt10","sw","fw","ts","vg","-o"},MID(A2,6,4)),{"Meraki Router","Switch","Router","Switch","Firewall","TermServer","Voice Gateway","TermServer"})

You are legend, thanks for the tip and solution.