Lookup Two Columns

[macshimi]

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

 

[Bob Phillips]

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&M1,$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)

 

[Vito]

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.

 

[Roger Govier]

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.

 

[Arvi Laanemets]

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)

 

[macshimi]

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