SUMPRODUCT ?

[Guest]

I have 3 columns of data, NAME, DATE,VALUE

NAME Date Text
Carlos 1/1/4 10
Carlos 2/1/4 20
Carlos 5/6/4 30
Carlos 6/6/4 40
Peter 5/5/4 50
Peter 20/6/4 60

The formula that I need to use is

sumproduct((NAME=Carlos)*(DATES=MAX(DATES)),VALUES)

Look for CARLOS in the database, and retrieve VALUE when NAME is CARLOS
and DATE is the LATEST (for carlos of course).

That doesn't work becuase DATES=MAX(DATES) should be related to NAMES = Carlos through a MATCH formula probably.I think it is because it doesn't match the latest date for carlos. I can't make it work

In this example if I look for Carlos the data I want is 40
If I look for Peter the data I want should be 60

Any ideas will be very much appreciated.

Thanks and regards,
Carlos.

 

[Bernard Liengme]

Hello Carlos
With "Carlos" or "Peter" in E1, this worked for me:
=VLOOKUP(MAX(IF(NAME=E1,Date,0)),B2:C7,2,FALSE)
Remember to enter it as array formula

Best wishes

--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address

I have 3 columns of data, NAME, DATE,VALUE

NAME Date Text
Carlos 1/1/4 10
Carlos 2/1/4 20
Carlos 5/6/4 30
Carlos 6/6/4 40
Peter 5/5/4 50
Peter 20/6/4 60

The formula that I need to use is

sumproduct((NAME=Carlos)*(DATES=MAX(DATES)),VALUES)

Look for CARLOS in the database, and retrieve VALUE when NAME is CARLOS
and DATE is the LATEST (for carlos of course).

That doesn't work becuase DATES=MAX(DATES) should be related to NAMES =

Carlos through a MATCH formula probably.I think it is because it doesn't
match the latest date for carlos. I can't make it work