Excel - Look up last value

Common problems – different language?
Let's talk in English.

Excel - Look up last value

Poslaťod Palo-admin » 26.10.2010, 09:35

Posted by Mel

In Sheet one I have this:

A B
NAME Dates
Joe 10/01/10
Bob 09/30/10
Mark 10/05/10
Joe 09/30/10

And on Sheet 2 i have
A B
Joe
Bob
Mark

In B on sheet two I want it to return the last date that the name was present


When the list of source data is sorted, solution is simple,
you can use VLOOKUP, or OFFSET(or INDEX) combined with MATCH.

But, when list is unsorted, to use pure Excel formulas without macro is more complicated.
Then combined formula, where SUMPRODUCT is used might be solution.

See sample:
http://pc-prog.eu/phpBB3/images/xMyKB/Lookup_Last.xls

TIP: to make formula not so immense, use named range, e.g. range with source names = xName

Palo
Nemáte oprávnenie prezerať súbory priložené v tomto príspevku.
Palo-admin
Administrátor stránky
 
Príspevky: 473
Registrovaný: 18.07.2008, 16:43
Udelené poďakovania: 6 krát
Prijaté poďakovania: 63 krát

Späť na Solving MS Office problems in English

Kto je on-line

Užívatelia prezerajúci fórum: Google [Bot] a 1 hosť

cron