Can't add wildcards to index match

[Odlanier]

Hi all,

I'm hoping some can help

I am trying to do a two criteria index match in VBA.

This works:

result = [INDEX('Sheet1'!b:b,MATCH(1,(ah5='Sheet1'!A:A)*(x5='Sheet1'!B:B),0))]
Range("bb5").Value = result



But when I alter it to introduce wildcards into the second criteria it stops working.


result = [INDEX('Sheet1'!b:b,MATCH(1,(ah5='Sheet1'!A:A)*("*" & x5 & "*" ='Sheet1'!B:B),0))]
Range("bb5").Value = result

By rights this should work. It's such a small issue but its taking up a lot of my time.

Thanks in advance.

[Squeaky]

Try... result = [INDEX('Sheet1'!b:b,MATCH(1,(ah5='Sheet1'!A:A)*(""*"" & x5 & ""*"" ='Sheet1'!B:B),0))]
Range("bb5").Value = result

Squeaky

[XLent]

I would say that unless the asterisks are literal, e.g. "*apple*" is the value stored in Sheet1!b:b, this won't work - even if the syntax is fixed (c/o doubling up on the quotations)

I suspect you will need to revert to a SEARCH based approach, e.g.:

result = [INDEX('Sheet1'!b:b,MATCH(1,('sheet1'!a:a=ah5)*ISNUMBER(SEARCH(x5,'Sheet1'!b:b)),0))]

edit: just realised this is quite an old post so OP will have hopefully resolved in the intervening period ;-)