Lookup Nth Match Multiple Criteria

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,

Master
Current
Department
Name
Master
Line Number
Master
Line Name
FC Sales TWFC Sales vs LW
NIGHTWEAR01H05MBLEESPRESSO YOURSELF0-7
NIGHTWEAR01T05MNUDSHORT SATIN JERSEY6-6
NIGHTWEAR01M25LPNKC+T ESME VINTAGE3-6
NIGHTWEAR01T03MBLETRSR JER OXF STRIPE0-6
NIGHTWEAR01T03MBLETRSR JER OXF STRIPE0-6

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

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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..

DataResult
Criteria1Criteria2Line No.Criteria1Criteria2Line No.
A2Line1A5Line7
A4Line2A5Line8
A1Line3A4Line2
A3Line4A1Line3
A2Line5A1Line9
A2Line6B5Line12
A5Line7B5Line15
A5Line8B4Line14
A1Line9B3Line11
A5Line10B1Line16
B3Line11C5Line26
B5Line12C4Line24
B3Line13C3Line23
B4Line14C2Line27
B5Line15C2Line28
B1Line16
B2Line17
B2Line18
B2Line19
B1Line20
C1Line21
C1Line22
C3Line23
C4Line24
C3Line25
C5Line26
C2Line27
C2Line28
C4Line29
C5Line30

<tbody>
</tbody>
 
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
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.
 
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

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

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