# Index Match Match Max

#### Maci3j

##### Board Regular
Hi everyone,

I'm looking for a array formula which shows the latest delivery date of a purchase order in the RESULT column.
I need to use the Match function since the positions of columns can always change.

The exercise is as follow:

I came up with the following formula. The problem is that I don't know how to apply the MAX function.
=INDEX(\$A\$3:\$B\$6;MATCH(\$D4;\$A\$3:\$A\$6;0);MATCH(E\$2;\$A\$2:\$B\$2;0))

May I ask you for help please?
Thank you in advance.

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### kweaver

##### Well-known Member

Code:
``=MAXIFS(\$B\$3:\$B\$9,\$A\$3:\$A\$9,D3)``

It would help if your profile indicated which version of Excel you're running as MAXIFS isn't available in all versions.

Last edited:

#### KRice

##### Well-known Member
Here is another idea to consider...edited to reflect your comment about matching the column heading...
MrExcel20210312.xlsx
ABCDE
1DATARESULT
2PO & Posnr.Delivery DatePO & Posnr.Delivery Date
3a9/23/2019a11/1/2020
4a10/31/2019b2/5/2021
5b9/24/2020c9/1/2020
6b2/5/2021
7a11/1/2020
8c9/1/2020
9c6/6/2020
Sheet2
Cell Formulas
RangeFormula
E3:E5E3=AGGREGATE(14,6,(\$A\$2:\$B\$2=E\$2)*(\$A\$3:\$B\$9)/(\$A\$3:\$A\$9=D3),1)

Last edited:

#### Maci3j

##### Board Regular

Code:
``=MAXIFS(\$B\$3:\$B\$9,\$A\$3:\$A\$9,D3)``

It would help if your profile indicated which version of Excel you're running as MAXIFS isn't available in all versions.
It's Excel Microsoft 365 and an xlsx. type file

#### Peter_SSs

##### MrExcel MVP, Moderator

It's Excel Microsoft 365
Please add that to your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using. (Don’t forget to scroll down & ‘Save’)
eg

#### Maci3j

##### Board Regular
Please add that to your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using. (Don’t forget to scroll down & ‘Save’)
eg
View attachment 34407
done

#### Fluff

##### MrExcel MVP, Moderator

Thanks for updating your profile, do either of the suggestions work for you?

#### Maci3j

##### Board Regular
no not really. Since the position of the Delivery date can swith to another column. That's why Match function would be usefull.

#### Fluff

##### MrExcel MVP, Moderator
You don't need the match function. The formula provided by KRice does what you've asked for
+Fluff 1.xlsm
ABCDEF
1DATARESULT
2PO & Posnr.Delivery DatePO & Posnr.Delivery Date
3a23/09/2019a01/11/2020
4a31/10/2019b05/02/2021
5b24/09/2020c01/09/2020
6b05/02/2021
7a01/11/2020
8c01/09/2020
9c06/06/2020
10
11
Main
Cell Formulas
RangeFormula
F3:F5F3=AGGREGATE(14,6,(\$A\$2:\$C\$2=F\$2)*(\$A\$3:\$C\$9)/(\$A\$3:\$A\$9=E3),1)

#### KRice

##### Well-known Member
Please let us know if it doesn't work for some reason. As shown in the follow-up example by @Fluff, the ranges in the formula are extended in two places such that they include the cell whose column heading matches that of the desired return column. Your example indicates that you want to find "Delivery Date" in cell E\$2. The formula I posted includes the terms
(\$A\$2:\$B\$2=E\$2)*(\$A\$3:\$B\$9)
The first of those terms forms an array showing which column in the range \$A\$2:\$B\$2 matches E\$2, and which columns do not. This avoids the MATCH function, but effectively achieves the same result.

In the demonstration posted by @Fluff, a new column was inserted, which shifts the location of "Delivery Date" to cell F\$2, and the full data table expands such that it is found in columns A:C. Therefore the formula adjusts to cover this expanded range:
(\$A\$2:\$C\$2=F\$2)*(\$A\$3:\$C\$9)
Again, the first term forms an array showing which column(s) in \$A\$2:\$C\$2 match F\$2.

Just be sure to confirm that the second term in parentheses covers the same columns as the first term in parentheses...in the example from @Fluff, the 1st term in parentheses covers A:C, so the 2nd term in parentheses must also cover A:C.

Here is another example with the column of interest (Delivery Date) somewhere in the middle of a larger range that involves columns A:E:
MrExcel20210312.xlsx
ABCDEFGHI
1DATARESULT
2PO & Posnr.QtyProductDelivery DateDescriptionPO & Posnr.Delivery Date
3a9/23/2019a11/1/2020
4a10/31/2019b2/5/2021
5b9/24/2020c9/1/2020
6b2/5/2021
7a11/1/2020
8c9/1/2020
9c6/6/2020
Sheet2
Cell Formulas
RangeFormula
I3:I5I3=AGGREGATE(14,6,(\$A\$2:\$E\$2=I\$2)*(\$A\$3:\$E\$9)/(\$A\$3:\$A\$9=H3),1)

Replies
6
Views
111
Replies
8
Views
93
Replies
8
Views
72
Replies
3
Views
48
Replies
8
Views
131

1,130,156
Messages
5,640,453
Members
417,143
Latest member

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

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