Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Two Columns
I have data arranged with the first two Columns showing Employee ID and Pay
Date; the other columns show different deductions. I need to lookup the Employee ID and then the Pay Date, once those two are established, to look across the row to the required deduction: Emp ID Pay Pension Union Tax Health 1 Sep-05 5 0.2 9 2 1 Oct-05 6 0.3 6 5 1 Nov-05 7 0.2 5 4 1 Dec-05 8 0.2 8 3 2 Sep-05 9 0.3 7 11 2 Oct-05 6 0.2 3 0.3 2 Nov-05 5 0.2 9 6 2 Dec-05 8 0.3 4 5 If it is not possible in this layout (the data was extracted from a payroll program) how do I re-arrange the data to make it accessable? thanks in adavnce. Charles |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Two Columns
Assuming the employee is in L1, the date in M1, this returns total
deductions =INDEX($D$2:$D$20+$E$2:$E$20+$F$2:$F$20,MATCH(L1&M 1,$A$2:$A$20&$B$2,0)) it is an array formula, so commit with Ctrl-Shift-Enter. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "macshimi" wrote in message ... I have data arranged with the first two Columns showing Employee ID and Pay Date; the other columns show different deductions. I need to lookup the Employee ID and then the Pay Date, once those two are established, to look across the row to the required deduction: Emp ID Pay Pension Union Tax Health 1 Sep-05 5 0.2 9 2 1 Oct-05 6 0.3 6 5 1 Nov-05 7 0.2 5 4 1 Dec-05 8 0.2 8 3 2 Sep-05 9 0.3 7 11 2 Oct-05 6 0.2 3 0.3 2 Nov-05 5 0.2 9 6 2 Dec-05 8 0.3 4 5 If it is not possible in this layout (the data was extracted from a payroll program) how do I re-arrange the data to make it accessable? thanks in adavnce. Charles |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Two Columns
Firstly, Insert a column before the table and concatenate the EmployeeID and PayDate using =X2&Y2 copied down, assuming the table begins at X2 Now for your vlookup, use this =Vlookup(A2&B2,$W$2:$AC$1000,2,False) where A2 and B2 contains the EmpID and PayDate to lookup and W2:AC1000 contains the lookup table, including the newly inserted column. The 2 is column index within the table which contains the info to pull. Btw, the new column can be hidden. -- Vito ------------------------------------------------------------------------ Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182 View this thread: http://www.excelforum.com/showthread...hreadid=497537 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Two Columns
Hi
With data in A2:F9, I set up the Employee required in H2, Pay Period in I2 then in cell J2 enter this array formula (Commit with Ctrl+Shift+Enter, and EXcel will enter the { } curly braces. Do not type them yourself. Alos use Ctrl+ShifT+Enter if you amend the formula) {=INDEX($A$2:$F$9,MATCH($H2&$I2,$A$2:$A$9&$B$2:$B$ 9),COLUMN()-7)} Copy across though cells K2:M2 to extract the suceeding columns of data from the main table. -- Regards Roger Govier macshimi wrote: I have data arranged with the first two Columns showing Employee ID and Pay Date; the other columns show different deductions. I need to lookup the Employee ID and then the Pay Date, once those two are established, to look across the row to the required deduction: Emp ID Pay Pension Union Tax Health 1 Sep-05 5 0.2 9 2 1 Oct-05 6 0.3 6 5 1 Nov-05 7 0.2 5 4 1 Dec-05 8 0.2 8 3 2 Sep-05 9 0.3 7 11 2 Oct-05 6 0.2 3 0.3 2 Nov-05 5 0.2 9 6 2 Dec-05 8 0.3 4 5 If it is not possible in this layout (the data was extracted from a payroll program) how do I re-arrange the data to make it accessable? thanks in adavnce. Charles |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Two Columns
Hi
When the return value is a number, then (assuming source table is on sheet Data, searched ID is in cell A2, and serached date in cell B2) Pension=SUMPRODUCT(--(Data!$A$2:$A$100=A2),--(Data!$B$2:$B$100=B2),Data!$C$2:$C$100) Union Tax=SUMPRODUCT(--(Data!$A$2:$A$100=A2),--(Data!$B$2:$B$100=B2),Data!$D$2:$D$100) Health=SUMPRODUCT(--(Data!$A$2:$A$100=A2),--(Data!$B$2:$B$100=B2),Data!$E$2:$E$100) -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "macshimi" wrote in message ... I have data arranged with the first two Columns showing Employee ID and Pay Date; the other columns show different deductions. I need to lookup the Employee ID and then the Pay Date, once those two are established, to look across the row to the required deduction: Emp ID Pay Pension Union Tax Health 1 Sep-05 5 0.2 9 2 1 Oct-05 6 0.3 6 5 1 Nov-05 7 0.2 5 4 1 Dec-05 8 0.2 8 3 2 Sep-05 9 0.3 7 11 2 Oct-05 6 0.2 3 0.3 2 Nov-05 5 0.2 9 6 2 Dec-05 8 0.3 4 5 If it is not possible in this layout (the data was extracted from a payroll program) how do I re-arrange the data to make it accessable? thanks in adavnce. Charles |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Two Columns
Thank very much for all the input, guys.
I will try the solutions on Monday and let you know if I have succeeded. Regards Charles |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Arithmetical Mode of Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
Hidden Columns in Shared Workbooks | Excel Discussion (Misc queries) | |||
Lookup function skipping columns | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Lookup Function Problem | Excel Discussion (Misc queries) |