My Maxifs formula not pulling data from the latest date

B

Barb Mick

New Member
Joined
Jan 25, 2018
Messages
14
Office Version
  1. 365
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))
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
try this:
Mr excel questions 64.xlsm
ADL
1Date PurchasedItem #Price
22023-09-25MZ002014
32023-08-22AL002115
42023-08-29CO002212
52023-09-26RQ002312
62023-09-24XG002411
72023-08-26JC002514
82023-08-30AL002110
92023-09-28CO002214
102023-08-20RQ002314
112023-09-23CS002912
122023-08-23ZN003010
132023-09-24AL002114
142023-08-27CO002213
152023-08-28RQ002315
162023-09-22HE003415
172023-09-27ED003512
182023-08-28AL002115
192023-08-25CO002215
202023-09-28RQ002312
212023-08-27CX003914
222023-08-28AL002114
232023-09-23CO002215
242023-09-29RQ002313
252023-09-29EV004311
262023-09-24QG004411
Barb Mick Wine



Mr excel questions 64.xlsm
EL
1Item #
2MZ00202023-09-25
3RQ00232023-09-29
4XG00242023-09-24
5CO00222023-09-28
6CS00292023-09-23
7AL00212023-09-24
8HE00342023-09-22
9ED00352023-09-27
10RQ00232023-09-29
11CO00222023-09-28
12RQ00232023-09-29
13EV00432023-09-29
14QG00442023-09-24
Barb Mick Sept2023
Cell Formulas
RangeFormula
L2:L14L2=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))
 
Upvote 0
Try:
Also, I would not use whole column references as this can slow down your workbook.

Book4
DEO
1Item#Price
215021
Sept2023
Cell Formulas
RangeFormula
O2O2=XLOOKUP(MAXIFS(Wine!A:A,Wine!D:D,E2)&"\"&E2,Wine!A:A&"\"&Wine!D:D,Wine!$L:$L)


Book4
ABCDKL
1Purchase DateItem#Price
26/12/2023100$ 12.00
36/22/2023225$ 22.00
47/5/2023150$ 21.00
57/10/2023225$ 26.00
68/5/2023225$ 28.00
78/6/2023100$ 15.00
88/7/2023225$ 28.00
Wine
 
Upvote 0
HEre is an updated MAXIFS function next to the FILTER Function method.

Mr excel questions 64.xlsm
ADL
1Date PurchasedItem #Price
22023-09-25MZ002014
32023-08-22AL002115
42023-08-29CO002212
52023-09-26RQ002312
62023-09-24XG002411
72023-08-26JC002514
82023-08-30AL002110
92023-09-28CO002214
102023-08-20RQ002314
112023-09-23CS002912
122023-08-23ZN003010
132023-09-24AL002114
142023-08-27CO002213
152023-08-28RQ002315
162023-09-22HE003415
172023-09-27ED003512
182023-08-28AL002115
192023-08-25CO002215
202023-09-28RQ002312
212023-08-27CX003914
222023-08-28AL002114
232023-09-23CO002215
242023-09-29RQ002313
252023-09-29EV004311
262023-09-24QG004411
Barb Mick Wine





Mr excel questions 64.xlsm
ELMN
1Item #Using FilterUsing MaxIFS
2MZ00202023-09-252023-09-25
3RQ00232023-09-292023-09-29
4XG00242023-09-242023-09-24
5CO00222023-09-282023-09-28
6CS00292023-09-232023-09-23
7AL00212023-09-242023-09-24
8HE00342023-09-222023-09-22
9ED00352023-09-272023-09-27
10RQ00232023-09-292023-09-29
11CO00222023-09-282023-09-28
12RQ00232023-09-292023-09-29
13EV00432023-09-292023-09-29
14QG00442023-09-242023-09-24
Barb Mick Sept2023
Cell Formulas
RangeFormula
L2:L14L2=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:N14N2=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 )
 
Upvote 1
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!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Flag the latest correct value from grouped rows with criteria
Replies
5
Views
388
Fluff
Fluff
S
  • Solved
Copying sheet row when date has been reached
Replies
3
Views
708
saileen
S
C
  • Question Question
Return closest date based on three criteria being true, or return "no date found"
Replies
8
Views
292
chris_bosten
C
T
  • Question Question
Multi-Formula Question - Prices/Date
2
Replies
15
Views
545
Fluff
Fluff
A
  • Solved
Filter formula based on drop down BUT with an option for all data
Replies
7
Views
239
myall_blues
myall_blues

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

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
Back
Top