SUMPRODUCT ?

L

litos_aldovea

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.
 
B

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


litos_aldovea said:
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 =
Click to expand...
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
 
B

Bernard Liengme

We need an array here not for the VLOOKUP but for the IF which otherwise
will return just one value - we need it to return an array.

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


litos_aldovea said:
Hi Bernard and Art,

Before anything else, thanks a lot for you help, and most important, for your time.

Both formulas work OK! on this small sample of data. I am going to use
Click to expand...
this logig in my model to see how it goes.
Bernard, regarding your Vlookup formula, I don't understand why it is an
Click to expand...
array {} formula. Is there a logic for using a formula as an array or not?
Do you know where can I documented myself on the subject?
 
Top