Finding the nth match of muliple criteria (but wildcards are needed)

[Timmtamm]

I can find the 1st match of my data, but I'm having trouble finding the 2nd, 3rd, etc.

Column A has a name, with a random number at the end (separated by a colon). Example: "Name1 : 283"
Column B has a name based off of a type of result. Example: "Type 10"
Column C has my results I'm looking for.

My formula for the 1st instance looks like this:
Where E1 is the 1st criteria "Name1"
and E2 is the 2nd criteria "Type 02"

{=INDEX(Sheet1!$C$1:$C$999,MATCH($E$1&"*"&"|"&$E$2&"*",Sheet1!$A1:$A999&"|"&Sheet1!$B1:$B999,0))}

How can I find the nth instance?

I tried this but it doesn't work with the wildcard.

=INDEX(Sheet1!O1:O999,SMALL(IF(Sheet1!$A1:$A999=($E$5&"*"),IF(Sheet1!C1:C999=$C$7,ROW(Sheet1!P1:P999))),3))

A simplified table looks like this (looking for the 3rd value of Name1):

	Column A	Column B	Column C	Column D	Column E
1	Name1 : 283	Type 02	21.25		Name1
2	Name1 : 283	Type 10	58.21		Type 15
3	Name1 : 187	Type 15	11.85
4	Name1 : 187	Type 21	78.36		Result Value
5	Name2 : 155	Type 10	70.22		11.85
6	Name2 : 155	Type 11	92.18
7	Name2 : 741	Type 21	44.32
8	Name3 : 533	Type 02	18.64
9	Name3 : 533	Type 10	27.36
10	Name3 : 432	Type 11	1.87
11	Name3 : 432	Type 15	97.12
12	Name3 : 432	Type 21	17.64
13	Name4 : 381	Type X	#
14	Name5 : 631	Type X	#
15	Name5 : 374	Type X	#
16	Name5 : 877	Type X	#
17	Name6 : 678	Type X	#
18	Name7 : 258	Type X	#
19	Name7 : 258	Type X	#
20	Name8 : 211	Type X	#

<tbody>
</tbody>

 

[Timmtamm]

I guess I failed to mention... Some of the "types" are sub lettered, so like "Type 10a" or "Type 10b." That's why I need a wildcard for that column as well.

Oh, and the failed version of the formula was posted incorrectly. It's actually:

=INDEX(C1:C999,SMALL(IF($A1:$A999=($E$5&"*"),IF(C1:C999=$C$7,ROW(A1:A999))),3))

 

[Timmtamm]

Ugg, keep catching my mistakes after I can't edit any longer. My appologies. The failed formula is actually:

{=INDEX(C1:C999,SMALL(IF($A1:$A999=($E$1&"*"),IF(C1:C999=($E$2&"*"),ROW(A1:A999))),3))}

 

[FDibbins]

Based on your sample...

	E​	F​	G​
1​	Name1
2​	Type 15	11.85​
3​		11.85​	1​
4​	Result Value
5​	11.85

F2=INDEX(C:C,SMALL(IF((LEFT($A$1:$A$20,LEN($E$1))=$E$1)*($B$1:$B$20=$E$2),ROW($A$1:$A$20)),ROWS($A$1:A1)))
enter use CSE

If you want the nth value - assuming you have more than 1 match (your sample did not)...
F3=INDEX(C:C,SMALL(IF((LEFT($A$1:$A$20,LEN($E$1))=$E$1)*($B$1:$B$20=$E$2),ROW($A$1:$A$20)),G3))
G3 would contain the nth value you wanted

 

[Aladin Akyurek]

{=INDEX(Sheet1!$C$1:$C$999,MATCH($E$1&"*"&"|"&$E$2&"*",Sheet1!$A1:$A999&"|"&Sheet1!$B1:$B999,0))}

Like this:

Control+shift+enter, not just enter:

=INDEX(Sheet1!$C$1:$C$999,SMALL(IF(ISNUMBER(SEARCH($E$1&"*"&"|"&$E$2,Sheet1!$A1:$A999&"|"&Sheet1!$B1:$B999)),ROW(Sheet1!$C$1:$C$999)-ROW(Sheet1!$C$1)+1),3))

which looks for the 3rd occurrence.

 

[Timmtamm]

Both answers work well. Thanks!

 

[FDibbins]

happy to help, thanks for the feedback

 

[kjahmad]

Hope someone can assist me with this, struggling so much that had to join and post the question!

How can this be amended to look horizontally instead of vertically?

 

[Aladin Akyurek]

Hope someone can assist me with this, struggling so much that had to join and post the question!

How can this be amended to look horizontally instead of vertically?

Care to post a small sample along with the expected outcome ?