Lookup Nth Match Multiple Criteria

L

Lewzerrrr

Active Member
Joined
Jan 18, 2017
Messages
256
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]
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
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)))
 
Last edited:
Upvote 0
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]
 
Last edited:
Upvote 0

Book1
ABCDEFG
1DataResult
2Criteria1Criteria2Line No.Criteria1Criteria2Line No.
3A2Line1A5Line7
4A4Line2Line8
5A1Line3Line10
6A3Line4
7A2Line5
8A2Line6
9A5Line7
10A5Line8
11A1Line9
12A5Line10
13B3Line11
14B5Line12
15B3Line13
16B4Line14
17B5Line15
18B1Line16
19B2Line17
20B2Line18
21B2Line19
22B1Line20
23C1Line21
24C1Line22
25C3Line23
26C4Line24
27C3Line25
28C5Line26
29C2Line27
30C2Line28
31C4Line29
32C5Line30
Sheet1
Cell Formulas
RangeFormula
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
 
Upvote 0
Then I don't understand what you're trying to do. There are three matches for those criteria in your example.

WBD
 
Upvote 0
wideboydixon said:
Then I don't understand what you're trying to do. There are three matches for those criteria in your example.

WBD
Click to expand...

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.
 
Upvote 0
OK. I had to add a helper column which you could hide:


Book1
ABCDEFGH
1DataResult
2Criteria1Criteria2Line No.Criteria1Criteria2Line No.Helper
3A2Line1A5Line71
4A4Line2A5Line82
5A1Line3A4Line21
6A3Line4A1Line31
7A2Line5A1Line92
8A2Line6B5Line121
9A5Line7B5Line152
10A5Line8B4Line141
11A1Line9B3Line111
12A5Line10B1Line161
13B3Line11C5Line261
14B5Line12C4Line241
15B3Line13C3Line231
16B4Line14C2Line271
17B5Line15C2Line282
18B1Line16
19B2Line17
20B2Line18
21B2Line19
22B1Line20
23C1Line21
24C1Line22
25C3Line23
26C4Line24
27C3Line25
28C5Line26
29C2Line27
30C2Line28
31C4Line29
32C5Line30
Sheet1
Cell Formulas
RangeFormula
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
VBA - Match two values from Table1 to two in Table2 and move/align
2
Replies
15
Views
374
Welshy1491
W
P
  • Question Question
Populating, aggregating and sorting multiple matching values in Excel
Replies
0
Views
220
Pbobas
P
M
  • Question Question
Macro or Power Automate to Identify and Move "Washes" from the "Master" Sheet to the "Washes" Sheet
Replies
0
Views
192
Madam_Xavier
M
C
  • Solved
VBA Code to move Amount Due from individual sheets to a master sheet, and then copy information from the master sheet back to the individual sheets?
Replies
7
Views
393
Coyotex3
C
k3yn0t3
  • Solved
INDEX(MATCH()) pulling wrong row data & Macro to duplicate sheets with formulas
Replies
4
Views
574
Alex Blakenburg
A

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top