Lookup Troubles Again

H

H

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
 
J

JulieD

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$1000)

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Reference cells in seperate worksheet 3
look up table 1
Lookup related question 1
Vlookup in another sheet? 1
vlookup and validation lists 1
How do I sum here using criteria there? 10
problem with excel logic, too complicated of a function set??? 3
matching & combining data from seperate worksheets 2

Top