Lookup Nth Match Multiple Criteria

[Lewzerrrr]

Hey,

I can't work this one out and I've looked everywhere but can't wrap my brain around it properly.

I'm trying to return a value with 2 criteria and moving past the duplicates, from a standard INDEX MATCH SMALL IF ROW the ROW at the end returns the array of the SMALL but it's not moving past the duplicate.

Sample data with formulas in B2:B6 =INDEX(Dump!$G$5:$G$13147,SMALL(IF(Dump!$D$5:$D$13147=A2,IF(Dump!$R$5:$R$13147=E2,ROW(Dump!$G$5:$G$13147)-ROW(Dump!$G$5)+1)),ROW($1:1))) confirmed with CSE.

The -6 is a duplicate and I can't get past it, any help? If I hardcore the ROW($1:1) to 1 and then change the formula in B6 to a 2 it finds the duplicate.

Thanks,

[TABLE="width: 433"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Master
Current
Department
Name[/TD]
[TD]Master
Line Number[/TD]
[TD]Master
Line Name[/TD]
[TD]FC Sales TW[/TD]
[TD]FC Sales vs LW[/TD]
[/TR]
[TR]
[TD]NIGHTWEAR[/TD]
[TD]01H05MBLE[/TD]
[TD]ESPRESSO YOURSELF[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-7[/TD]
[/TR]
[TR]
[TD]NIGHTWEAR[/TD]
[TD]01T05MNUD[/TD]
[TD]SHORT SATIN JERSEY[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]-6[/TD]
[/TR]
[TR]
[TD]NIGHTWEAR[/TD]
[TD]01M25LPNK[/TD]
[TD]C+T ESME VINTAGE[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]-6[/TD]
[/TR]
[TR]
[TD]NIGHTWEAR[/TD]
[TD]01T03MBLE[/TD]
[TD]TRSR JER OXF STRIPE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-6[/TD]
[/TR]
[TR]
[TD]NIGHTWEAR[/TD]
[TD]01T03MBLE[/TD]
[TD]TRSR JER OXF STRIPE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-6[/TD]
[/TR]
</tbody>[/TABLE]

 

[wideboydixon]

ROWS() not ROW() - the latter will always return 1!!

WBD

 

[Lewzerrrr]

ROWS gives me num error as soon as I drag down! I guess because it's trying to return the 2nd, 3rd match and so on but there isn't one

=INDEX(Dump!$G$5:$G$13147,SMALL(IF(Dump!$D$5:$D$13147=A2,IF(Dump!$R$5:$R$13147=E2,ROW(Dump!$G$5:$G$13147)-ROW(Dump!$G$5)+1)),ROWS($1:1)))

 

[Lewzerrrr]

Here's a better data sample to offer. Any help is greatly appreciated

As you can see, there's 2 matches of A and 5 so I would need to return Line 7 and Line 8..

[TABLE="width: 448"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Data[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"]Result[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl66"]Criteria1[/TD]
[TD="class: xl66"]Criteria2[/TD]
[TD="class: xl66"]Line No.[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]Criteria1[/TD]
[TD="class: xl66"]Criteria2[/TD]
[TD="class: xl66"]Line No.[/TD]
[/TR]
[TR]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]Line1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]Line7[/TD]
[/TR]
[TR]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]Line2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]Line8[/TD]
[/TR]
[TR]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]Line3[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]Line2[/TD]
[/TR]
[TR]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]Line4[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]Line3[/TD]
[/TR]
[TR]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]Line5[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]Line9[/TD]
[/TR]
[TR]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]Line6[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]B[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]Line12[/TD]
[/TR]
[TR]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]Line7[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]B[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]Line15[/TD]
[/TR]
[TR]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]Line8[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]B[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]Line14[/TD]
[/TR]
[TR]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]Line9[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]B[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]Line11[/TD]
[/TR]
[TR]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]Line10[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]B[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]Line16[/TD]
[/TR]
[TR]
[TD="class: xl66"]B[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]Line11[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]C[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]Line26[/TD]
[/TR]
[TR]
[TD="class: xl66"]B[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]Line12[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]C[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]Line24[/TD]
[/TR]
[TR]
[TD="class: xl66"]B[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]Line13[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]C[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]Line23[/TD]
[/TR]
[TR]
[TD="class: xl66"]B[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]Line14[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]C[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]Line27[/TD]
[/TR]
[TR]
[TD="class: xl66"]B[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]Line15[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]C[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]Line28[/TD]
[/TR]
[TR]
[TD="class: xl66"]B[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]Line16[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66"]B[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]Line17[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66"]B[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]Line18[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66"]B[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]Line19[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66"]B[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]Line20[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66"]C[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]Line21[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66"]C[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]Line22[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66"]C[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]Line23[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66"]C[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]Line24[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66"]C[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]Line25[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66"]C[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]Line26[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66"]C[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]Line27[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66"]C[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]Line28[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66"]C[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]Line29[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66"]C[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]Line30[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]

 

[wideboydixon]

Book1
	A	B	C	D	E	F	G
1	Data				Result
2	Criteria1	Criteria2	Line No.		Criteria1	Criteria2	Line No.
3	A	2	Line1		A	5	Line7
4	A	4	Line2				Line8
5	A	1	Line3				Line10
6	A	3	Line4
7	A	2	Line5
8	A	2	Line6
9	A	5	Line7
10	A	5	Line8
11	A	1	Line9
12	A	5	Line10
13	B	3	Line11
14	B	5	Line12
15	B	3	Line13
16	B	4	Line14
17	B	5	Line15
18	B	1	Line16
19	B	2	Line17
20	B	2	Line18
21	B	2	Line19
22	B	1	Line20
23	C	1	Line21
24	C	1	Line22
25	C	3	Line23
26	C	4	Line24
27	C	3	Line25
28	C	5	Line26
29	C	2	Line27
30	C	2	Line28
31	C	4	Line29
32	C	5	Line30
Sheet1

Cell Formulas
Range	Formula
G3	{=IFERROR(INDEX($C$3:$C$32,SMALL(IF($A$3:$A$32=$E$3,IF($B$3:$B$32=$F$3,ROW($C$3:$C$32)-ROW($C$3)+1)),ROWS($1:1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[Lewzerrrr]

Thanks but not quite the same result

 

[wideboydixon]

Then I don't understand what you're trying to do. There are three matches for those criteria in your example.

WBD

 

[Lewzerrrr]

Then I don't understand what you're trying to do. There are three matches for those criteria in your example.

WBD

Yes but I'm only trying to find the matches based on the criteria.. refer to sample data where end result is shown.

The criteria changes from A 5, A 5 to A 4, A 1, A 1, B 5.. yours gives me the 3 matches based on just A5 which isn't what I need.

I need to return the 1st match of A 5 and then 2nd as it's duplicate and then 1st of A 4 as it's unique, 1st of A 1 and 2nd of A 1 as it's duplicate.

 

[wideboydixon]

OK. I had to add a helper column which you could hide:

Book1
	A	B	C	D	E	F	G	H
1	Data				Result
2	Criteria1	Criteria2	Line No.		Criteria1	Criteria2	Line No.	Helper
3	A	2	Line1		A	5	Line7	1
4	A	4	Line2		A	5	Line8	2
5	A	1	Line3		A	4	Line2	1
6	A	3	Line4		A	1	Line3	1
7	A	2	Line5		A	1	Line9	2
8	A	2	Line6		B	5	Line12	1
9	A	5	Line7		B	5	Line15	2
10	A	5	Line8		B	4	Line14	1
11	A	1	Line9		B	3	Line11	1
12	A	5	Line10		B	1	Line16	1
13	B	3	Line11		C	5	Line26	1
14	B	5	Line12		C	4	Line24	1
15	B	3	Line13		C	3	Line23	1
16	B	4	Line14		C	2	Line27	1
17	B	5	Line15		C	2	Line28	2
18	B	1	Line16
19	B	2	Line17
20	B	2	Line18
21	B	2	Line19
22	B	1	Line20
23	C	1	Line21
24	C	1	Line22
25	C	3	Line23
26	C	4	Line24
27	C	3	Line25
28	C	5	Line26
29	C	2	Line27
30	C	2	Line28
31	C	4	Line29
32	C	5	Line30
Sheet1

Cell Formulas
Range	Formula
H3	=IF(AND($E3=$E2,$F3=$F2),H2+1,1)
G3	{=IFERROR(INDEX($C$3:$C$32,SMALL(IF($A$3:$A$32=$E3,IF($B$3:$B$32=$F3,ROW($C$3:$C$32)-ROW($C$3)+1)),$H3)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[Lewzerrrr]

Thanks, works a charm! Really dislike helper columns but beggars can’t be choosers!