How to extract multiple values (exc duplicates) with two criteria

[danhendo888]

Book1
	A	B										L
1	Vendor	Invoice										Reference
2	1000010034	2216705										8190052331
Burnard

Cell Formulas
Range	Formula
L2	L2	=(INDEX(Sheet2!$L$3:$L$21835, SMALL(IF(FREQUENCY(IF(Sheet2!$K$3:$K$21835=$B2, Sheet2!$L$3:$L$21835), Sheet2!$L$3:$L$21835), ROW(Sheet2!$L$3:$L$21835)-ROW(Sheet2!$L$3)+1), COLUMNS($L2:L2))))
Press CTRL+SHIFT+ENTER to enter array formulas.

Above is what I am using currently.
Searching with one criteria; the invoice number.

Is it possible to search using two criteria? (invoice number + vendor number)
The tricky part is that there are duplicate values in column L for a given invoice.
If possible, it should look something like the below.

Book1
	I		K	L	M
1	Vendor		Invoice	Reference
2	1000010034		2216705	8190052331
3	1000010034		2216705	8190052331
4	1000010034		2216705	8190000001
5	1000010034		2216705	8190000001
6
7
8	Output:
9	Vendor		Invoice	Reference
10	1000010034		2216705	8190000001	8190052331
Sheet2

 

[Marcelo Branco]

Too late

M.

 

[danhendo888]

here is a video for this case: Video

Thank you for creating the video tutorial, sandy666. I really appreciate it
Would have taken me weeks to figure out on my own.

 

[sandy666]

You are welcome

Have a nice day

 

[danhendo888]

Those video are not just light weekend reading : ( Video #9 alone would take the whole weekend, as it is about a lot of the nuts and bolts of M Code. For # 9 video, I think the pdf notes are 40 pages. The reason this class is not like the basics videos of cleaning and loading is because there is A LOT to learn and learning new things takes time. But if you want to learn, it is there for you for free, danhendo888 : )

Yes, I noticed that the MSPTDA playlist was 66 videos long, more than a dozen of which relate to Power Query.
It will take me weeks (if not months) to absorb the lessons, but that's okay because, well.. you guessed it, Excelisfun ©

Also, thank you so much for the updated formulas below. They work beautifully. I appreciate it

Formula in cell B16: =SUM(--(FREQUENCY(MATCH($B$5:$B$11&$C$5:$C$11,$B$5:$B$11&$C$5:$C$11,0),ROW($B$5:$B$11)-ROW($B$4))>0))

Formula in cell B19: =IF(ROWS(B$19:B19)>$B$16,"",INDEX(B$5:B$11,AGGREGATE(15,6,(ROW($B$5:$B$11)-ROW($B$4))/(FREQUENCY(MATCH($B$5:$B$11&$C$5:$C$11,$B$5:$B$11&$C$5:$C$11,0),ROW($B$5:$B$11)-ROW($B$4))>0),ROWS(B$19:B19))))

Formula in cell D19: =IF(C19="","",SUM(--(FREQUENCY(IF(($B$5:$B$11=$B19)*($C$5:$C$11=$C19),$D$5:$D$11),IF(($B$5:$B$11=$B19)*($C$5:$C$11=$C19),$D$5:$D$11))>0)))

Formula in cell E19: =IF(OR($D19="",COLUMNS($E19:E19)>$D19),"",INDEX($D$5:$D$11,SMALL(IF(FREQUENCY(IF(($B$5:$B$11=$B19)*($C$5:$C$11=$C19),MATCH($D$5:$D$11,$D$5:$D$11,0)),ROW($D$5:$D$11)-ROW($D$4)),ROW($D$5:$D$11)-ROW($D$4)),COLUMNS($E19:E19))))

 

[mgirvin]

danhendo888,

If you want to learn, that class is there for you : )

No worries about the formulas, because you had awesome MVP Marcelo Branco to walk you through a solution : )