How to return multiple unique invoice numbers of a sales order

[ameenuksg]

Hi All - I tried using index match to return matching results for sales order# 133213 which is duplicated in the row below in Sheet 1, Column A. But it always returns only the first invoice# it could find on Sheet 2, Column A for both rows in Sheet 1, Column F. Appreciate any help, and thank you so much in advance ?

Column A	Column F	Column A	Column E
Sales Order#	Invoice#	Invoice#	Sales Order#
133213		6700000456	133213
133213		6700000443	133213
121323		6700000478	121323
Sheet 1	Sheet 1	Sheet 2	Sheet 2

 

[Toadstool]

Hi Ameenuksg,

You'll need to use SMALL to find the first, then second, etc. or use the SMALL option of AGGREGATE (16) as I've done in my example.

Data in Sheet2

Book1
	A	B	C	D	E
1	Invoice#				Sales Order#
2	6700000456				133213
3	6700000443				133213
4	6700000478				121323
Sheet2

Here's the formulae in Sheet1

Ameenuksg.xlsx
	A	B	C	D	E	F
1	Sales Order#					Invoice#
2	133213					6700000456
3	133213					6700000443
4	121323					6700000478
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=INDEX(Sheet2!$A$2:$A$999,AGGREGATE(15,6,ROW(Sheet2!$E$2:$E$999)-1/(A2=Sheet2!$E$2:$E$999),COUNTIF($A$2:$A2,A2)))

You'll get a #NUM error if there's no match or second match but you can wrap an IFERROR around to trap.

 

[ameenuksg]

Hi Thank you so much for your help, I tried your formula but it seems to return a result from a cell 2 rows after although there is only 1 result for this match.

Here is the formula I had incorporated from yours:
=IFERROR(INDEX(SalesWotherView[Billing document],AGGREGATE(15,6,ROW(SalesWotherView[Sales Order])-1/([@[SO'#]]=SalesWotherView[Sales Order]),COUNTIF($E$274:$E274,E274))),"no invoice")

 

[ameenuksg]

Here are pictures of the actual sheets.

Sheet 1 = 'Orders'
Sheet 2 = 'Sales w other view'

 

[Toadstool]

It's because your headings are on row 3 and not row 1. I'm not going to retype all the data so let me try and duplicate with my small dataset:

Sheet2 data

Ameenuksg-v2.xlsx
	A	B	C	D	E
1
2
3	Billing Document	B	C	D	Sales Order#
4	6700000456				133213
5	6700000443				133213
6	6700000478				121323
Sheet2

Sheet1 formula

Ameenuksg-v2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Account#	B	C	D	SO#	F	G	Invoice#	I	J	K	L	M	N	Invoice#2
2	8383838	22	77	88	133213	88	88	88							6700000456
3	9292929	33	66	99	133213	99	99	99							6700000443
4	4545372	44	55	111	121323	111	111	111							6700000478
Sheet1

Cell Formulas
Range	Formula
O2:O4	O2	=INDEX(Billing_Document,AGGREGATE(15,6,ROW(Sales_Order)-ROW(Table1[[#Headers],[Sales Order'#]])/([@[SO'#]]=Sales_Order),COUNTIF($E$2:$E2,[@[SO'#]])))

Named Ranges
Name	Refers To	Cells
Billing_Document	=Sheet2!$A$4:$A$6	O2:O4
Sales_Order	=Sheet2!$E$4:$E$6	O2:O4

If you install XL2BB it would be easier to see your data and Named Ranges.

 

[ameenuksg]

=INDEX(SalesWotherView[Billing document],AGGREGATE(15,6,ROW(SalesWotherView[Sales Order])-ROW(SalesWotherView[#Headers],[SO'#])/([@[SO'#]]=SalesWotherView[Sales Order]),COUNTIF($E$274:$E274,[@[SO'#]])))

I don't know whats wrong with the above formula that I typed in, but it shades the above part of the code shown in red and says that I have entered too many arguments for this function?

 

[Toadstool]

I can't see how you've named your tables/columns but because you're starting your headings on row 3 you could just replace
-ROW(SalesWotherView[#Headers],[SO'#])
with
-3

 

[ameenuksg]

Got it, I think I didnt understand how row function worked so now I know after removing '[SO'#]' from the row function and left it with '-ROW(SalesWotherView[#Headers])' only and its working wonders now. Cant thank you enough for putting the time and effort to help me?

 

[Toadstool]

You're welcome!

 

[ameenuksg]

Hi - What if I want to look up sales order# of another table too, so how do I improve on the formula above.