Lookup Troubles Again
 

Hi everyone, I've been trying but failing to remember how to solve my
problem. Can you help ?

On one worksheet I have a table composed of member id number, name and
annual salary history (one column for each year eg 2005,2004,2003)
On a second worksheet I have a straight list where member id may be repeated
multiple times in column A, with a date in column B and salary value in
column C

Please can you suggest a formula which would populate my table with the
correct salary history. Column headings on the table do match the values in
column B on the list.

Any help appreciated, many thanks - Harry

Hi Harry

assuming on your summary worksheet the first cell for 2005 that you want
populated is D2 and the member ID is in column B ...
then the formula would be
=SUMPRODUCT(--(Sheet2!$A$2:$A$1000=$B2),--(YEAR(Sheet2!$B$2:$B$1000)=D$1),Sheet2!$C$2:$C$100 0)

this formula can then be filled down and across ...
check out
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
for details on the sumproduct function

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"H" wrote in message
...
Hi everyone, I've been trying but failing to remember how to solve my
problem. Can you help ?

On one worksheet I have a table composed of member id number, name and
annual salary history (one column for each year eg 2005,2004,2003)
On a second worksheet I have a straight list where member id may be
repeated
multiple times in column A, with a date in column B and salary value in
column C

Please can you suggest a formula which would populate my table with the
correct salary history. Column headings on the table do match the values
in
column B on the list.

Any help appreciated, many thanks - Harry