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

#### Will85

##### Board Regular
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?

#### Fluff

##### MrExcel MVP, Moderator
Are you using the same formula for both columns J and K?
Yup.
Can you isolate the formula for just column J,
Jus change the range from B2:C100 to B2:B100

### 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
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
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
Oops forgot it had the index, try
Excel Formula:
``=INDEX(SORT(FILTER(B2:B100,A2:A100=I2),1,-1),SEQUENCE(10))``

#### Will85

##### Board Regular
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
Sort, Filter & Sequence are dynamic array functions & they were released Nov/Dec 2019, so they are fairly new functions.

Replies
10
Views
368
Replies
5
Views
119
Replies
2
Views
93
Replies
7
Views
81
Replies
5
Views
240

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.

### Which adblocker are you using?

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

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