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

#### Maci3j

##### Board Regular
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)
dear Fluff,

thank you for this magic formula! It is a great solution for my problem.
It works like a charm.

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### Fluff

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.
It is in fact the same formulae that @KRice posted in post#3

#### Maci3j

##### Board Regular
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)
Dear KRice,

you were right. Fluff's formula does exactly what I needed.
I would like to thank you for your remark and the important note 'Just be sure to confirm that the second term in parentheses covers the same columns as the first term in parentheses'

Replies
6
Views
112
Replies
8
Views
93
Replies
8
Views
74
Replies
3
Views
49
Replies
8
Views
132

1,130,323
Messages
5,641,531
Members
417,215
Latest member
Diaryman

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