Sumproduct / Max array formula problem

[Andibevan]

Hi All,

I have a spreadsheet with 3 named ranges:-

File_Name (name of file)
Version (file version)
Date_Val (date when version was saved).

For a given file (Test File1.txt) how do I return the version number of the
most recently saved document?

Thanks in advance

Andi

 

[Krishnakumar]

Hi Andi,

If you sort the data in ascending order with the key Date_Val,

try this,

=LOOKUP(2,1/(A2:A30=D1),B2:B30)

where D1 houses the file name

HTH

 

[Biff]

Hi!

Array entered:

=INDEX(Version,MATCH(MAX(IF(File_Name="Test File
1.txt",Date_Val)),Date_Val,0))

Biff

 

[Harlan Grove]

Biff wrote...

Array entered:

=INDEX(Version,MATCH(MAX(IF(File_Name="Test File 1.txt",Date_Val)),
Date_Val,0))

....

LOOKUP wouldn't need array entry.

=LOOKUP(2,1/(File_Name="Test File 1.txt")
/(Date_Val=MAX(((File_Name="Test File 1.txt"))*Date_Val)),Version)

Also, you've got a bug in your formula. If any files appeared before
'Test File 1.txt' and some versions of those preceding files had the
same date as the latest version of 'Test File 1.txt', then your formula
would return the version from the topmost other file with that date.
For example,

FileName Version Date
foobar 1 8/22/2005
foobar 2 8/23/2005
foobar 3 8/24/2005
Test File 1.txt 1 8/17/2005
Test File 1.txt 2 8/24/2005

Your formula would return 3, not 2. In order to avoid that problem,
your INDEX formula would need to be revised as

=INDEX(Version,MATCH(MAX(IF(File_Name="Test File 1.txt",Date_Val)),
IF(File_Name="Test File 1.txt",Date_Val),0))

At this point the LOOKUP formula is shorter, and it doesn't need array
entry.

 

[Biff]

Good catch!

Easily fixed using either your modified suggestion or:

=INDEX(Version,MATCH(MAX(IF(File_Name="Test File
1.txt",Date_Val)),(File_Name="Test File 1.txt")*Date_Val,0))

At this point the LOOKUP formula is shorter, and it doesn't need array
entry.

Another good point!

I'm not comfortable using that Lookup technique just yet. I don't fully
understand how to test for multiple criteria.

One of these days!

Biff

 

[Harlan Grove]

Biff wrote...
....

Easily fixed using either your modified suggestion or:

=INDEX(Version,MATCH(MAX(IF(File_Name="Test File 1.txt",
Date_Val)),(File_Name="Test File 1.txt")*Date_Val,0))

which you could reduce further to

=INDEX(Version,MATCH(MAX((File_Name="Test File 1.txt")*Date_Val),
(File_Name="Test File 1.txt")*Date_Val,0))

I'm not comfortable using that Lookup technique just yet. I don't fully
understand how to test for multiple criteria.

If the multiple criteria would always result in a single entry
satisfying the criteria or all matches would be equally acceptable,

LOOKUP(2,1/((CriteriaExpression1)*(criteriaExpression2)*...),ReturnValues)