software

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 IDPayPensionUnionTaxHealth
1Sep-0550.292
1Oct-0560.365
1Nov-0570.254
1Dec-0580.283
2Sep-0590.3711
2Oct-0560.230.3
2Nov-0550.296
2Dec-0580.345

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

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(L1amp;M 1,$A$2:$A$20amp;$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)

quot;macshimiquot; gt; wrote in message
...
gt; I have data arranged with the first two Columns showing Employee ID and
Pay
gt; Date; the other columns show different deductions.
gt;
gt; I need to lookup the Employee ID and then the Pay Date, once those two are
gt; established, to look across the row to the required deduction:
gt;
gt; Emp ID Pay Pension Union Tax Health
gt; 1 Sep-05 5 0.2 9 2
gt; 1 Oct-05 6 0.3 6 5
gt; 1 Nov-05 7 0.2 5 4
gt; 1 Dec-05 8 0.2 8 3
gt; 2 Sep-05 9 0.3 7 11
gt; 2 Oct-05 6 0.2 3 0.3
gt; 2 Nov-05 5 0.2 9 6
gt; 2 Dec-05 8 0.3 4 5
gt;
gt; If it is not possible in this layout (the data was extracted from a
payroll
gt; program) how do I re-arrange the data to make it accessable?
gt;
gt; thanks in adavnce.
gt;
gt; Charles

Firstly,

Insert a column before the table and concatenate the EmployeeID and
PayDate using =X2amp;Y2 copied down, assuming the table begins at X2

Now for your vlookup, use this =Vlookup(A2amp;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: www.excelforum.com/member.php...oamp;userid=29182
View this thread: www.excelforum.com/showthread...hreadid=497537Hi

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($H2amp;$I2,$A$2:$A$9amp;$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 gt; wrote:
gt; I have data arranged with the first two Columns showing Employee ID
gt; and Pay Date; the other columns show different deductions.
gt;
gt; I need to lookup the Employee ID and then the Pay Date, once those
gt; two are established, to look across the row to the required deduction:
gt;
gt; Emp ID Pay Pension Union Tax Health
gt; 1 Sep-05 5 0.2 9 2
gt; 1 Oct-05 6 0.3 6 5
gt; 1 Nov-05 7 0.2 5 4
gt; 1 Dec-05 8 0.2 8 3
gt; 2 Sep-05 9 0.3 7 11
gt; 2 Oct-05 6 0.2 3 0.3
gt; 2 Nov-05 5 0.2 9 6
gt; 2 Dec-05 8 0.3 4 5
gt;
gt; If it is not possible in this layout (the data was extracted from a
gt; payroll program) how do I re-arrange the data to make it accessable?
gt;
gt; thanks in adavnce.
gt;
gt; Charles
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.laanemetslt;atgt;tarkon.ee )
quot;macshimiquot; gt; wrote in message
...
gt;I have data arranged with the first two Columns showing Employee ID and Pay
gt; Date; the other columns show different deductions.
gt;
gt; I need to lookup the Employee ID and then the Pay Date, once those two are
gt; established, to look across the row to the required deduction:
gt;
gt; Emp ID Pay Pension Union Tax Health
gt; 1 Sep-05 5 0.2 9 2
gt; 1 Oct-05 6 0.3 6 5
gt; 1 Nov-05 7 0.2 5 4
gt; 1 Dec-05 8 0.2 8 3
gt; 2 Sep-05 9 0.3 7 11
gt; 2 Oct-05 6 0.2 3 0.3
gt; 2 Nov-05 5 0.2 9 6
gt; 2 Dec-05 8 0.3 4 5
gt;
gt; If it is not possible in this layout (the data was extracted from a
gt; payroll
gt; program) how do I re-arrange the data to make it accessable?
gt;
gt; thanks in adavnce.
gt;
gt; Charles
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