Maxifs one less than max, two less than max, etc

Will85

Board Regular
Joined
Apr 26, 2012
Messages
191
Office Version
  1. 365
Platform
  1. Windows
I am trying to create a tool for users to view the $ value of each of the 10 most recent customer orders.

My accounting data is on sheet2: column A contains the customer number, Column B contains the order number, and column C contains the $ value

On sheet1 I have a drop down box in cell A1, this drop down box looks up a unique list of customer numbers contained on sheet3.

On sheet 1 In cells B1:B10 I want each of the 10 most recent $ values of each order. B1 is the most recent, B2 is the second most recent, B3 third most recent, etc.

I can use the maxif formula to find the maximum order number for a given customer, I know that is my most recent order.

However, I dont know what the second most recent order number is for this particular customer.

Im not sure if this is possible to do without a macro. The drop down will be used rapidly to change between customers. I need the results to update as the customer is changed.

Do I need a macro that is triggered by changing my combobox drop down?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Will85

Board Regular
Joined
Apr 26, 2012
Messages
191
Office Version
  1. 365
Platform
  1. Windows
Yup.

Jus change the range from B2:C100 to B2:B100
That works, its giving me the correct Order number, but its giving me a refed out value in the second column. I only need one column
 

Will85

Board Regular
Joined
Apr 26, 2012
Messages
191
Office Version
  1. 365
Platform
  1. Windows
all I need is column J from your example, which its doing, but it also automatically gave me values in the next column. Ive never seen a formula like this.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,792
Office Version
  1. 365
Platform
  1. Windows
Oops forgot it had the index, try
Excel Formula:
=INDEX(SORT(FILTER(B2:B100,A2:A100=I2),1,-1),SEQUENCE(10))
 

Will85

Board Regular
Joined
Apr 26, 2012
Messages
191
Office Version
  1. 365
Platform
  1. Windows
Oops forgot it had the index, try
Excel Formula:
=INDEX(SORT(FILTER(B2:B100,A2:A100=I2),1,-1),SEQUENCE(10))
That worked, thank you! Is this a new type of formula for Office 360? Ive never seen something like this.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,792
Office Version
  1. 365
Platform
  1. Windows
Sort, Filter & Sequence are dynamic array functions & they were released Nov/Dec 2019, so they are fairly new functions.
 

Forum statistics

Threads
1,147,823
Messages
5,743,409
Members
423,792
Latest member
travisds

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top