index-match where match needs to find substring

[Machtyn]

Is there a way to use an Index-Match formula where the Match needs to match on a substring in the cell?

For example, I have the following data
SOURCESHEET:

group	position	data
My Group: group1	1	this is my data
My Group: group1	2	more data
My Group: group2	2	la-di-da
My Group: group2	1	tiddly-winks
My Group: group3	1	Funky dance!
My Group: group3	2	More dance!
Another Group: group1	1	Data comes in
Another Group: group1	2	Data goes out

Of course, in another worksheet, I will want to match group1 and extract the value in the data column at that cell.
If I was performing an exact match, it would look like:

Please Login or Register  to view this content.

where 'DESTSHEET'!A2 has the value of "group1". Of course, this doesn't work because column the column I'm trying to match on has 'My Group: group1'.

Is there a formulaic solution to my situation?

As you may surmise, I will have to take that result and refine it against the Position column. But I'll should be able to figure that out after I solve this substring match issue.

[daddylonglegs]

You can use a "wildcard" with MATCH, try

=INDEX('SOURCESHEET'!A:C,MATCH("*"&'DESTSHEET'!A2&"*",'SOURCESHEET'!A:A,0),3)

[oeldere]

Make a helpcolumn in D and add:

Please Login or Register  to view this content.

and drag down.

then you can search on exact match group1

[Machtyn]

The wildcard method worked perfectly! Unfortunately, I now have to figure out how to do a multiple column search.

I found some instruction which states to create an array result with MATCH:
MATCH(1,(conditional1)*(conditional2),0).

This thread can be considered closed.

[daddylonglegs]

Note: for multiple conditions in that syntax wildcards won't work, you can try the SEARCH function, e.g.

=INDEX('SOURCESHEET'!C2:C100,MATCH(1,ISNUMBER(SEARCH('DESTSHEET'!A2,'SOURCESHEET'!A2:A100))*('SOURCESHEET'!B2:B100=2),0))

confirmed with CTRL+SHIFT+ENTER