Find the Nth Largest with Multiple Criteria

[*shudder*]

Hi All,

I would like to search a table and return the Nth largest number between to dates, whilst also matching the customers name and/or if the order value is over a certain amount.

Sample data set as follows:

Order_Name	Order_Date	Order_Value
Smith	01/01/2022​	1,000.00​
Smith	01/02/2022​	2,000.00​
Smith	01/04/2022​	3,000.00​
Jones	01/01/2022​	4,000.00​
Jones	01/02/2022​	5,000.00​
Jones	01/04/2022​	6,000.00​
Jones	01/01/2022​	7,000.00​
Smith	01/02/2022​	8,000.00​
Kaur	01/04/2022​	9,000.00​
Singh	01/01/2022​	10,000.00​
Kaur	01/02/2022​	11,000.00​
Kaur	01/04/2022​	12,000.00​
Smith	01/01/2023​	12,000.00​
Smith	01/02/2023​	11,000.00​
Smith	01/04/2023​	10,000.00​
Jones	01/01/2023​	9,000.00​
Jones	01/02/2023​	8,000.00​
Jones	01/04/2023​	7,000.00​
Jones	01/01/2023​	6,000.00​
Smith	01/02/2023​	5,000.00​
Kaur	01/04/2023​	4,000.00​
Singh	01/01/2023​	3,000.00​
Kaur	01/02/2023​	2,000.00​
Kaur	01/04/2023​	1,000.00​

Expected results:

Name	Smith		Name	Kaur
Start Period	01/01/2022​		Start Period	01/01/2023​
End Period	31/12/2022​		End Period	31/12/2023​
Orders Over	0​		Order Value	1500​
Result			Result
1st Highest	8000​		1st Highest	4000​
2nd Highest	3000​		2nd Highest	2000​
3rd Highest	2000​		3rd Highest	Nil

I am using Excel 2016.

 

[nemmi69]

The formula for the nth largest is =LARGE(range, cell with nth to find) IE =LARGE($C5:$G5,I$4) where $C5:$G5 is the range and $I4 contains the nth number

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E
1	Order_Name	Order_Date	Order_Value
2	Smith	01/01/2022	1,000.00
3	Smith	01/02/2022	2,000.00
4	Smith	01/04/2022	3,000.00
5	Jones	01/01/2022	4,000.00
6	Jones	01/02/2022	5,000.00
7	Jones	01/04/2022	6,000.00
8	Jones	01/01/2022	7,000.00
9	Smith	01/02/2022	8,000.00
10	Kaur	01/04/2022	9,000.00
11	Singh	01/01/2022	10,000.00
12	Kaur	01/02/2022	11,000.00
13	Kaur	01/04/2022	12,000.00
14	Smith	01/01/2023	12,000.00
15	Smith	01/02/2023	11,000.00
16	Smith	01/04/2023	10,000.00
17	Jones	01/01/2023	9,000.00
18	Jones	01/02/2023	8,000.00
19	Jones	01/04/2023	7,000.00
20	Jones	01/01/2023	6,000.00
21	Smith	01/02/2023	5,000.00
22	Kaur	01/04/2023	4,000.00
23	Singh	01/01/2023	3,000.00
24	Kaur	01/02/2023	2,000.00
25	Kaur	01/04/2023	1,000.00
26
27	Expected results:
28	Name	Smith		Name	Kaur
29	Start Period	01/01/2022		Start Period	01/01/2023
30	End Period	31/12/2022		End Period	31/12/2023
31	Orders Over	0		Order Value	1500
32
33	Result			Result
34	1st Highest	8000		1st Highest	4000
35	2nd Highest	3000		2nd Highest	2000
36	3rd Highest	2000		3rd Highest	Nil
sheet6

Cell Formulas
Range	Formula
B34:B36	B34	=IFERROR(AGGREGATE(14,6,$C$2:$C$25/($A$2:$A$25=$B$28)/($B$2:$B$25>=$B$29)/($B$2:$B$25<=$B$30)/($C$2:$C$25>$B$31),ROWS(B$34:B34)),"Nil")
E34:E36	E34	=IFERROR(AGGREGATE(14,6,$C$2:$C$25/($A$2:$A$25=$E$28)/($B$2:$B$25>=$E$29)/($B$2:$B$25<=$E$30)/($C$2:$C$25>$E$31),ROWS(B$34:B34)),"Nil")

 

[*shudder*]

The formula for the nth largest is =LARGE(range, cell with nth to find) IE =LARGE($C5:$G5,I$4) where $C5:$G5 is the range and $I4 contains the nth number

Thanks, that only provides the nth largest number rather than also matching the additional criteria.

I have spent quite a few hours searching and trying to combine the large function with others to get the desired result to no avail. The closest I have got is this:

=LARGE(IF(Table1[Order_Name]=Customer,IF(Table1[Order_Date]>=Date_Start,Table1[Order_Total])),1)

However, this doesn't incorporate the upper date range and I cannot figure out how to amend the formula to incorporate this i.e. the following just returns #NUM! result:

=LARGE(IF(Table1[Order_Name]=Customer,IF(AND(Table1[Order_Date]>=Date_Start,Table1[Order_Date]<=Date_End),Table1[Order_Total])),1)

The above also doesn't take into consideration to only return orders above x value.

 

[nemmi69]

Just did it for the 1st for smith

 

[*shudder*]

How about

Thanks as ever Fluff, this works well.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[nemmi69]

Sorry made an error