My Maxifs formula not pulling data from the latest date

[Barb Mick]

I have 2 sheets.

The Wine tab lists purchased items

Col A is the date purchased
Col D is the Item #
Col L is the Price per item


The Sept2023 tab is the current month inventory list

Col E has the Item #

In Col O, I’m trying to look up the most recent purchase price from Col L from the Wine tab

If I use the following formula, it pulls the highest price, so my formula is incorrect. I'm hoping someone sees the error of my ways.


=MAX(MAXIFS(Wine!L:L,Wine!D:D,@E:E), MAXIFS(Wine!A:A,Wine!L:L,@D:D))

 

[awoohaw]

try this:

Mr excel questions 64.xlsm
	A			D								L
1	Date Purchased			Item #								Price
2	2023-09-25			MZ0020								14
3	2023-08-22			AL0021								15
4	2023-08-29			CO0022								12
5	2023-09-26			RQ0023								12
6	2023-09-24			XG0024								11
7	2023-08-26			JC0025								14
8	2023-08-30			AL0021								10
9	2023-09-28			CO0022								14
10	2023-08-20			RQ0023								14
11	2023-09-23			CS0029								12
12	2023-08-23			ZN0030								10
13	2023-09-24			AL0021								14
14	2023-08-27			CO0022								13
15	2023-08-28			RQ0023								15
16	2023-09-22			HE0034								15
17	2023-09-27			ED0035								12
18	2023-08-28			AL0021								15
19	2023-08-25			CO0022								15
20	2023-09-28			RQ0023								12
21	2023-08-27			CX0039								14
22	2023-08-28			AL0021								14
23	2023-09-23			CO0022								15
24	2023-09-29			RQ0023								13
25	2023-09-29			EV0043								11
26	2023-09-24			QG0044								11
Barb Mick Wine

Mr excel questions 64.xlsm
	E							L
1	Item #
2	MZ0020							2023-09-25
3	RQ0023							2023-09-29
4	XG0024							2023-09-24
5	CO0022							2023-09-28
6	CS0029							2023-09-23
7	AL0021							2023-09-24
8	HE0034							2023-09-22
9	ED0035							2023-09-27
10	RQ0023							2023-09-29
11	CO0022							2023-09-28
12	RQ0023							2023-09-29
13	EV0043							2023-09-29
14	QG0044							2023-09-24
Barb Mick Sept2023

Cell Formulas
Range	Formula
L2:L14	L2	=MAX(TAKE(FILTER('Barb Mick Wine'!$A$2:$L$26,(MONTH('Barb Mick Wine'!$A$2:$A$26)=9)* (E2='Barb Mick Wine'!$D$2:$D$26)),,1))

 

[AhoyNC]

Try:
Also, I would not use whole column references as this can slow down your workbook.

Book4
	D	E										O
1		Item#										Price
2		150										21
Sept2023

Cell Formulas
Range	Formula
O2	O2	=XLOOKUP(MAXIFS(Wine!A:A,Wine!D:D,E2)&"\"&E2,Wine!A:A&"\"&Wine!D:D,Wine!$L:$L)

Book4
	A	B	C	D							K	L
1	Purchase Date			Item#								Price
2	6/12/2023			100								$ 12.00
3	6/22/2023			225								$ 22.00
4	7/5/2023			150								$ 21.00
5	7/10/2023			225								$ 26.00
6	8/5/2023			225								$ 28.00
7	8/6/2023			100								$ 15.00
8	8/7/2023			225								$ 28.00
Wine

 

[awoohaw]

HEre is an updated MAXIFS function next to the FILTER Function method.

Mr excel questions 64.xlsm
	A			D								L
1	Date Purchased			Item #								Price
2	2023-09-25			MZ0020								14
3	2023-08-22			AL0021								15
4	2023-08-29			CO0022								12
5	2023-09-26			RQ0023								12
6	2023-09-24			XG0024								11
7	2023-08-26			JC0025								14
8	2023-08-30			AL0021								10
9	2023-09-28			CO0022								14
10	2023-08-20			RQ0023								14
11	2023-09-23			CS0029								12
12	2023-08-23			ZN0030								10
13	2023-09-24			AL0021								14
14	2023-08-27			CO0022								13
15	2023-08-28			RQ0023								15
16	2023-09-22			HE0034								15
17	2023-09-27			ED0035								12
18	2023-08-28			AL0021								15
19	2023-08-25			CO0022								15
20	2023-09-28			RQ0023								12
21	2023-08-27			CX0039								14
22	2023-08-28			AL0021								14
23	2023-09-23			CO0022								15
24	2023-09-29			RQ0023								13
25	2023-09-29			EV0043								11
26	2023-09-24			QG0044								11
Barb Mick Wine

Mr excel questions 64.xlsm
	E							L	M	N
1	Item #							Using Filter		Using MaxIFS
2	MZ0020							2023-09-25		2023-09-25
3	RQ0023							2023-09-29		2023-09-29
4	XG0024							2023-09-24		2023-09-24
5	CO0022							2023-09-28		2023-09-28
6	CS0029							2023-09-23		2023-09-23
7	AL0021							2023-09-24		2023-09-24
8	HE0034							2023-09-22		2023-09-22
9	ED0035							2023-09-27		2023-09-27
10	RQ0023							2023-09-29		2023-09-29
11	CO0022							2023-09-28		2023-09-28
12	RQ0023							2023-09-29		2023-09-29
13	EV0043							2023-09-29		2023-09-29
14	QG0044							2023-09-24		2023-09-24
Barb Mick Sept2023

Cell Formulas
Range	Formula
L2:L14	L2	=MAX(TAKE(FILTER('Barb Mick Wine'!$A$2:$L$26,(MONTH('Barb Mick Wine'!$A$2:$A$26)=9)* (E2='Barb Mick Wine'!$D$2:$D$26)),,1))
N2:N14	N2	=MAXIFS('Barb Mick Wine'!$A$2:$A$26, 'Barb Mick Wine'!$A$2:$A$26,">="&DATE(2023,9,1), 'Barb Mick Wine'!$A$2:$A$26,"<="&DATE(2023,9,30), 'Barb Mick Wine'!$D$2:$D$26,E2 )

 

[Barb Mick]

The XLOOKUP formula is spot on, and you're right about the whole column references. It slowed the spreadsheet beyond anything acceptable.
So I made that change.
Now I plan to try the MAX(TAKE(FILTER formula for the learning aspect.

Thank you both so much!

 

[AhoyNC]

Glad we could help. Thanks for the feedback.