Finding MAX value using an Array.

vaidmohammed

Member
Hi,
Attached is an excel where few common details are mentioned in Col. A and in Col. B corresponding dates are mentioned.
I need a formula in Col. E1 that, if I input any value of Col. A in Col. D1 then the system should show the max. date in Col E1.

I tried the following formula but it is not working.

={MAX(IF(D1="MV",(VLOOKUP(D1,A1:B12,2,0)),""))}

Attachments

• 16.1 KB Views: 10

Fluff13

Active Member
=MAX(IF(A1:A12=D1,B1:B12))

Confirmed with Ctrl Shift Enter

Or if you have the maxifs function
=MAXIFS(B1:B12,A1:A12,D1)

Peter Bartholomew

Well-Known Member
Your existing formula looks up a single value and then applies the MAX function, which is too late to be useful.
I would go with @Fluff13 's formulas
= MAXIFS(dates, details, criterion)
or for old versions of Excel
= MAX( IF(details=criterion, dates ) )

It is possible to use the look up functions though. The common strategy is that you must filter the dates first and then, to find the maximum, you look up a date that you know to be larger than anything that occurs in the table using an approximate match.
= LOOKUP( today, IF(details=criterion, dates) )
= LOOKUP( today, dates/(details=criterion) )
= VLOOKUP( today, IF(details=criterion, dates), 1)

Using Office 365
= XLOOKUP( today, dates/(details=criterion), dates, , -1 )
= XLOOKUP( today, IF(details=criterion, dates), dates, , -1 )

or finally,
= MAX( FILTER( dates, details=criterion ) )

Attachments

• 19.4 KB Views: 4

AlanSidman

Well-Known Member
With Power Query, Mcode follows
Code:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"Max Date", each List.Max([Column2]), type date}, {"Data", each _, type table [Column1=text, Column2=date]}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Data"})
in
#"Removed Columns``````
 v A B 1 Column1 Max Date 2 MV 1/17/2020 3 FV 1/19/2020 4 MS 1/21/2020 5 SV 1/23/2020

Peter Bartholomew

Well-Known Member
@AlanSidman
That would do it, especially if the data access requires an ETL step. Mind you, at least I get a rational date format of 17/1/2020 from PQ

If I upped the game with the formula
= XLOOKUP( IF({0,1},today,0), dates/(details=criterion), dates, , {1,-1} )
to simultaneously calculate earliest and latest dates, what would be the neatest PQ approach?

Khalid NGO

Excel Ninja
Hi,

Two more solutions (non array)

=AGGREGATE(14,6,1/(A1:A12=D1)*B1:B12,1)
=SUMPRODUCT(MAX((A1:A12=D1)*B1:B12))

Regards,

Peter Bartholomew

Well-Known Member
@Khalid NGO
Two more that I missed! Since I have yet to delete the workbook, I added
= AGGREGATE(LARGE, IgnoreErrors, 1/(details=criterion)*dates, 1)
= SUMPRODUCT( MAX((details=criterion)*dates) )

I would, though, classify both as array formulas. One of the few areas in which 'old Excel' actually worked correctly!

@AlanSidman
I also discovered that PQ performs multiple grouped calculations simultaneously.
Code:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"details", type text}, {"date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"details"}, {{"minimum", each List.Min([date]), type nullable date}, {"maximum", each List.Max([date]), type nullable date}})
in
#"Grouped Rows"``````
If I stick around long enough, I might even learn something!

Attachments

• 26.8 KB Views: 2

vaidmohammed

Member
Thank you, Experts and Camps, but I can't use Pivot in my report. I have to stick with VLOOKUP, as a change in the report means loads of work to be redone.

Making my case very simple, I am attaching new workbook where :

Worksheet DelvStatus: Contains delivery details, from where I am picking up date (col C) by referring col. B for VLOOKUP reference.
Worksheet Data: This sheet contains my actual raw data (trimmed to required one), where I am using the VLOOKUP formula in Col. H for getting the newest date.

Now the problem is that, for BTID 4778, it has been delivered 4 times on different dates (29-Nov-18, 21-Dec-18, 08-Jan-19, 27-Jan-19), which is recorded in DelvStatus sheet.
And in Data sheet I am expecting the newest date under Col. H for BTID 4778. However, I am getting the oldest date (29-Nov-2018). Here I am expecting 27-Jan-19.

Attachments

• 19.2 KB Views: 5

deciog

Active Member
Hi vaidmohammed

Use this way

=IFERROR((IF(G2="Delivered",LOOKUP(2,1/(DelvStatus!\$B:\$B=A2),DelvStatus!\$C:\$C),"")),"No Data Present")

Decio

Last edited:

Fluff13

Active Member
Did you try either of the formula I suggested?
They do exactly what you asked for.