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.
 

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
Joined
May 8, 2018
Messages
1,791
Office Version
  1. 365
  2. 2010
How about this in E3 and filled down?

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
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Apr 4, 2013
Messages
78
Office Version
  1. 365
How about this in E3 and filled down?

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
Joined
May 28, 2005
Messages
48,562
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
1615805279537.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,869
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Maci3j

Board Regular
Joined
Apr 4, 2013
Messages
78
Office Version
  1. 365
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
Joined
Jun 12, 2014
Messages
56,869
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

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

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