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

#### danhendo888

##### Board Regular
Book1
ABL
1VendorInvoiceReference
2100001003422167058190052331
Burnard
Cell Formulas
RangeFormula
L2L2=(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
IKLM
1VendorInvoiceReference
2100001003422167058190052331
3100001003422167058190052331
4100001003422167058190000001
5100001003422167058190000001
6
7
8Output:
9VendorInvoiceReference
101000010034221670581900000018190052331
Sheet2

Last edited by a moderator:

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### danhendo888

##### Board Regular
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.

You are welcome

Have a nice day

#### danhendo888

##### Board Regular
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

##### Well-known Member
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 : )

Replies
3
Views
146
Replies
2
Views
159
Replies
1
Views
80
Replies
8
Views
714
Replies
12
Views
469

1,127,121
Messages
5,622,861
Members
415,935
Latest member
kes1973

### 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.

### Which adblocker are you using?

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

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