Index Match Match Max

Maci3j

Board Regular
Joined
Apr 4, 2013
Messages
78
Office Version
  1. 365
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:
1615567533456.png


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.
 

Maci3j

Board Regular
Joined
Apr 4, 2013
Messages
78
Office Version
  1. 365
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
Joined
Jun 12, 2014
Messages
56,969
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
It is in fact the same formulae that @KRice posted in post#3
 

Maci3j

Board Regular
Joined
Apr 4, 2013
Messages
78
Office Version
  1. 365
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'
 

Watch MrExcel Video

Forum statistics

Threads
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.
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