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,

Master Current Department Name	Master Line Number	Master Line Name	FC Sales TW	FC Sales vs LW
NIGHTWEAR	01H05MBLE	ESPRESSO YOURSELF	0	-7
NIGHTWEAR	01T05MNUD	SHORT SATIN JERSEY	6	-6
NIGHTWEAR	01M25LPNK	C+T ESME VINTAGE	3	-6
NIGHTWEAR	01T03MBLE	TRSR JER OXF STRIPE	0	-6
NIGHTWEAR	01T03MBLE	TRSR JER OXF STRIPE	0	-6

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[Lewzerrrr]

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

	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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula H3 =IF(AND($E3=$E2,$F3=$F2),H2+1,1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula 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)),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

WBD

I’m not at a computer so can’t test this but instead of helper can’t I replace the $H3 with a COUNTIFS($E$3:E3,E3,$F$3:F3,F3) it produced the same sequence on my mobile version of Excel?

 

[wideboydixon]

Indeed you can.

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		A	5	Line8
5	A	1	Line3		A	4	Line2
6	A	3	Line4		A	1	Line3
7	A	2	Line5		A	1	Line9
8	A	2	Line6		B	5	Line12
9	A	5	Line7		B	5	Line15
10	A	5	Line8		B	4	Line14
11	A	1	Line9		B	3	Line11
12	A	5	Line10		B	1	Line16
13	B	3	Line11		C	5	Line26
14	B	5	Line12		C	4	Line24
15	B	3	Line13		C	3	Line23
16	B	4	Line14		C	2	Line27
17	B	5	Line15		C	2	Line28
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=$E3,IF($B$3:$B$32=$F3,ROW($C$3:$C$32)-ROW($C$3)+1)),COUNTIFS($E$3:$E3,$E3,$F$3:$F3,$F3))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD