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]

 

[Lewzerrrr]

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

	A	B	C	D	E	F	G	H
Data	Result
Criteria1	Criteria2	Line No.	Criteria1	Criteria2	Line No.	Helper
A	Line1	A	Line7
A	Line2	A	Line8
A	Line3	A	Line2
A	Line4	A	Line3
A	Line5	A	Line9
A	Line6	B	Line12
A	Line7	B	Line15
A	Line8	B	Line14
A	Line9	B	Line11
A	Line10	B	Line16
B	Line11	C	Line26
B	Line12	C	Line24
B	Line13	C	Line23
B	Line14	C	Line27
B	Line15	C	Line28
B	Line16
B	Line17
B	Line18
B	Line19
B	Line20
C	Line21
C	Line22
C	Line23
C	Line24
C	Line25
C	Line26
C	Line27
C	Line28
C	Line29
C	Line30

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"]5[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]4[/TD]

[TD="align: right"][/TD]

[TD="align: right"]5[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"]4[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"]5[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"][/TD]

[TD="align: right"]5[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"][/TD]

[TD="align: right"]4[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"][/TD]

[TD="align: right"]5[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"][/TD]

[TD="align: right"]4[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]4[/TD]

[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]

[TD="align: right"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]

[TD="align: right"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H3[/TH]
[TD="align: left"]=IF(AND($E3=$E2,$F3=$F2),H2+1,1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G3[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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