Lookup Function
 

Hi All,

I have a large table of data that contains the following information:-

Column 1 - File Name
Column 2 - Version Number
Column 3 - Date

Essentially it is a list of what documents were saved when and what
version number they were.

What formula could I use to find the maximum version number that is
between 2 specified dates.

I.e. the highest version of document1.doc that was saved between 1/1/04
and 1/2/04.

Any ideas?

Thanks

Andi


--
andibevan
------------------------------------------------------------------------
andibevan's Profile: http://www.excelforum.com/member.php...fo&userid=9882
View this thread: http://www.excelforum.com/showthread...hreadid=273131

Hi
try thwe following array formula(entered with cTRL+SHIFT+ENTER):
=MAX(IF((A1:A100="document1.doc")*(C1:C100=DATE(2 004,1,1))*(C1:C100<DATE(2004,2,1)),B1:B100))

"andibevan" wrote:


Hi All,

I have a large table of data that contains the following information:-

Column 1 - File Name
Column 2 - Version Number
Column 3 - Date

Essentially it is a list of what documents were saved when and what
version number they were.

What formula could I use to find the maximum version number that is
between 2 specified dates.

I.e. the highest version of document1.doc that was saved between 1/1/04
and 1/2/04.

Any ideas?

Thanks

Andi


--
andibevan
------------------------------------------------------------------------
andibevan's Profile: http://www.excelforum.com/member.php...fo&userid=9882
View this thread: http://www.excelforum.com/showthread...hreadid=273131