Formula to Return Name Based on Values and Dates

[BrettOlbrys1]

I have this table and the result I am looking to achieve is to reorder the list based on a date and the values (from highest to lowest). For example, if I want to return the top 3 deals from 3/1/21, I should see my result being:

Column E	Column F
Company 9	6
Company 7	4
Company 8	1

I have a formula (in column F) to order the #'s from greatest to least is: LARGE($B$1:$B$10,ROWS(F$4:$F4))

I then want to return the name of the company in column E that matches the result from column F. But the problem I run into is that if I want it to be based on a specific date, such as 3/1/21, it will still return the first value it finds in the list, so I would get Company 3, Company 1, and Company 2.

How do I get the correct company names, based on a specific date (column C), if there are similar qty's?

Company Name (A)	# of Widgets (B)	Date (C)
Company 1	4	1/1/21
Company 2	1	1/1/21
Company 3	6	1/1/21
Company 4	4	2/1/21
Company 5	1	2/1/21
Company 6	6	2/1/21
Company 7	4	3/1/21
Company 8	1	3/1/21
Company 9	6	3/1/21

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F
1	Company Name (A)	# of Widgets (B)	Date (C)			03/01/2021
2	Company 1	4	01/01/2021		Company 9	6
3	Company 2	1	01/01/2021		Company 7	4
4	Company 3	6	01/01/2021		Company 8	1
5	Company 4	4	02/01/2021
6	Company 5	1	02/01/2021
7	Company 6	6	02/01/2021
8	Company 7	4	03/01/2021
9	Company 8	1	03/01/2021
10	Company 9	6	03/01/2021
11
Main

Cell Formulas
Range	Formula
E2:F4	E2	=SORT(FILTER(A2:B10,C2:C10=F1),2,-1)
Dynamic array formulas.

 

[BrettOlbrys1]

Can you explain to me how the formula works because the columns I showed don't represent the full set of data, so I will need to adjust it?

 

[Fluff]

It filters columns A&B based on the values in col C, then sorts the result from high to low based on col B

 

[BrettOlbrys1]

It filters columns A&B based on the values in col C, then sorts the result from high to low based on col B

Thanks. One other question, if I want to limit the results to only the top 5 values for the specific date, how do I accomplish this?

 

[Fluff]

Could you have less than 5 values for a particular date?

 

[BrettOlbrys1]

Could you have less than 5 values for a particular date?

Could you have less than 5 values for a particular date?

It's possible, but not likely.

 

[Fluff]

In that case how about

+Fluff 1.xlsm
	A	B	C	D	E	F
1	Company Name (A)	# of Widgets (B)	Date (C)			02/01/2021
2	Company 1	4	01/01/2021		Company 6	6
3	Company 2	1	01/01/2021		Company 9	6
4	Company 3	6	01/01/2021		Company 4	4
5	Company 4	4	02/01/2021		Company 7	4
6	Company 5	1	02/01/2021		Company 5	1
7	Company 6	6	02/01/2021
8	Company 7	4	02/01/2021
9	Company 8	1	02/01/2021
10	Company 9	6	02/01/2021
11
Main

Cell Formulas
Range	Formula
E2:F6	E2	=INDEX(SORT(FILTER(A2:B10,C2:C10=F1),2,-1),SEQUENCE(5),{1,2})
Dynamic array formulas.

 

[BrettOlbrys1]

In that case how about

+Fluff 1.xlsm
	A	B	C	D	E	F
1	Company Name (A)	# of Widgets (B)	Date (C)			02/01/2021
2	Company 1	4	01/01/2021		Company 6	6
3	Company 2	1	01/01/2021		Company 9	6
4	Company 3	6	01/01/2021		Company 4	4
5	Company 4	4	02/01/2021		Company 7	4
6	Company 5	1	02/01/2021		Company 5	1
7	Company 6	6	02/01/2021
8	Company 7	4	02/01/2021
9	Company 8	1	02/01/2021
10	Company 9	6	02/01/2021
11
Main

Cell Formulas
Range	Formula
E2:F6	E2	=INDEX(SORT(FILTER(A2:B10,C2:C10=F1),2,-1),SEQUENCE(5),{1,2})
Dynamic array formulas.

This works perfectly, thank you!

 

[Fluff]

You're welcome & thanks for the feedback.