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

 

[BrettOlbrys1]

You're welcome & thanks for the feedback.

Fluff, I need your help again. Now, instead of filtering on one date, I want to filter on a range of dates. In your formula, where you have C2:C10=F1, I want to be able to have C2:C10>=first date AND C2:C10<final date. I tried to modify your formula with an AND statement, such as:

=INDEX(SORT(FILTER(A2:B10,AND(C2:C10>=F1,C2:C10<G1)),2,-1),SEQUENCE(5),{1,2})

It didn't work, so can you help me out?

Thanks

 

[Fluff]

It needs to be like

=INDEX(SORT(FILTER(A2:B10,(C2:C10>=F1)*(C2:C10<G1)),2,-1),SEQUENCE(5),{1,2})

 

[BrettOlbrys1]

It needs to be like

=INDEX(SORT(FILTER(A2:B10,(C2:C10>=F1)*(C2:C10<G1)),2,-1),SEQUENCE(5),{1,2})

Once again, perfect! Thanks

 

[Fluff]

You're welcome & thanks for the feedback.

 

[BrettOlbrys1]

One last thing, if possible. I have my file which contains lots of sheets (some are dashboards of the data, some are graphs, some are sheets that create relationships between the data, etc.), and one sheet drives everything.

That one sheet is where I import the external data, which then gets pushed to all of the other sheets because of formulas that read the data. That one import file sits on a SharePoint site (which gets updated daily), and I was wondering if there was a way for me to click a button within my Excel file that would grab the file, read the data, and then import it to my data sheet? Basically, I want to automate the process...is this possible and if so, how do I do that?

 

[Fluff]

As that's a totally different question, it needs a new thread. Thanks

 

[BrettOlbrys1]

As that's a totally different question, it needs a new thread. Thanks

OK, I will ask that in a different thread, but getting back to the latest formula:

=INDEX(SORT(FILTER(A2:B10,(C2:C10>=F1)*(C2:C10<G1)),2,-1),SEQUENCE(5),{1,2})

I want to now return a list of all rows when the total # of widgets is >=100. In the above formula, it limits the rows to (5), so how do I adjust the formula to do this?

Thanks

Brett

 

[Fluff]

You can do that like I originally showed.

 

[BrettOlbrys1]

You can do that like I originally showed.

Thanks, the issue I have is that the SORT function is returning every column in the data set, but I only want specific columns, not every column. If I had 10 columns of data, and I only wanted columns 1, 3, 6, 7, 9, and 10 as an example, how do I tell it what columns to return instead of every column?

 

[Fluff]

As that is a totally different question, it needs a new thread. Thanks.