Index match with wildcards in match array?

[bigbrownbear123]

Hello,

I would like to have an index match which will allow wildcards around the match array e.g.

=INDEX(Sheet2!$D$2:$D$3,MATCH($M2,"*"&Sheet2!$C$2:$C$3&"*",0))

I know this is not possible but is there some other formula which would achieve the same result (to match a text string to a sub-string of that text)?

Thanks

 

[Jon von der Heyden]

Hi

So I suggest that you build a sample as I describe, see if the suggested formula works for you, and then modify it to your exact requirement if it does.

Enter the following values in the following cells:
A1: Big Brown Bear
D1: Small
D2: Brown
D3: Pig

Now, enter the following formula (anywhere on the same sheet).

=LOOKUP(10^308,MATCH("*"&D1:D3&"*",A1,0),ROW(D1:D3)-ROW(D1)+1)

Which should yield: 2

MATCH returns the ordinal index position of an item in a table or array. If the lookup value exists more than once then it returns the position of the first find only.

My formula also returns the ordinal position of an item in the table, but it will return position of the last found item.

Hope this helps.