Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Lookup Lookup | Excel Worksheet Functions | |||
Lookup Function Problem | Excel Discussion (Misc queries) | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
Excel Lookup Functions | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |