Index Match Match Max

[Maci3j]

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.

 

[kweaver]

How about this in E3 and filled down?

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

 

[KRice]

Here is another idea to consider...edited to reflect your comment about matching the column heading...

MrExcel20210312.xlsx
	A	B	C	D	E
1	DATA			RESULT
2	PO & Posnr.	Delivery Date		PO & Posnr.	Delivery Date
3	a	9/23/2019		a	11/1/2020
4	a	10/31/2019		b	2/5/2021
5	b	9/24/2020		c	9/1/2020
6	b	2/5/2021
7	a	11/1/2020
8	c	9/1/2020
9	c	6/6/2020
Sheet2

Cell Formulas
Range	Formula
E3:E5	E3	=AGGREGATE(14,6,($A$2:$B$2=E$2)*($A$3:$B$9)/($A$3:$A$9=D3),1)

 

[Maci3j]

How about this in E3 and filled down?

=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]

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]

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]

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

 

[Maci3j]

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

 

[Fluff]

You don't need the match function. The formula provided by KRice does what you've asked for

+Fluff 1.xlsm
	A	B	C	D	E	F
1	DATA				RESULT
2	PO & Posnr.		Delivery Date		PO & Posnr.	Delivery Date
3	a		23/09/2019		a	01/11/2020
4	a		31/10/2019		b	05/02/2021
5	b		24/09/2020		c	01/09/2020
6	b		05/02/2021
7	a		01/11/2020
8	c		01/09/2020
9	c		06/06/2020
10
11
Main

Cell Formulas
Range	Formula
F3:F5	F3	=AGGREGATE(14,6,($A$2:$C$2=F$2)*($A$3:$C$9)/($A$3:$A$9=E3),1)

 

[KRice]

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
	A	B	C	D	E	F	G	H	I
1	DATA							RESULT
2	PO & Posnr.	Qty	Product	Delivery Date	Description			PO & Posnr.	Delivery Date
3	a			9/23/2019				a	11/1/2020
4	a			10/31/2019				b	2/5/2021
5	b			9/24/2020				c	9/1/2020
6	b			2/5/2021
7	a			11/1/2020
8	c			9/1/2020
9	c			6/6/2020
Sheet2

Cell Formulas
Range	Formula
I3:I5	I3	=AGGREGATE(14,6,($A$2:$E$2=I$2)*($A$3:$E$9)/($A$3:$A$9=H3),1)