index match with wildcards and multiple criteria

Question

I have used INDEX(MATCH()) formulas of this form before, but never with wildcards. Could anyone explain as two why adding in the A2&"*" would return an error value? I have checked the data and there definitely should be a match.

The formula is as below:

{=INDEX(I1:M1000,MATCH(1,(M1:M1000=Sheet3!B1)*(I1:I1000=A2&"*"),0),2)}

Answer 1

Suppose A2 contains "abc".

You can put a wild card in the search string of a match statement e.g.

MATCH(A2&"*",I1:I1000,0)

to search for anything beginning with abc, but not in the range that you're searching.

Also, the bracket

(I1:I1000=A2&"*")

is just comparing each cell in the range I1:I1000 with A2&"*" so in this context it just does a literal match of each cell with "abc*" and the * doesn't work as a wildcard.

You could try using FIND or SEARCH to do a partial match or using LEFT to get the first few characters of the strings in I1:I1000

=INDEX(I1:M1000,MATCH(1,(M1:M1000=B1)*(FIND(A2,I1:I1000)=1),0),2)

=INDEX(I1:M1000,MATCH(1,(M1:M1000=B1)*(LEFT(I1:I1000,LEN(A2))=A2),0),2)

You could also still use a wildcard if you re-cast the formula using an IF statement:-

=INDEX(I1:M1000,MATCH(A2&"*",IF(M1:M1000=B1,I1:I1000),0),2)