Formula refrencing cell with a value of adjacent cell

[Moshe Rosenberg]

I have 3 employees that get paid at three diferrent rates. I need to store
the name and rate in two columns, and then in three other columns put the
persons name and in an adjacent column put the amount of hours worked and in
another column the total money owed.

I could not figure out in the total box how to make a fomula to figure out
the rate of the employee. If I refrence the rate box it will not tell me the
employee name and if I show the employee name I don't get the rate.

What's the solution?

Thanks!

 

[Max]

One way is to use VLOOKUP
An example ..

Assume you have in Sheet1 in A1:B4,
the table below

Emp HourlyRate
ABC 10
DEF 20
XYZ 30

In Sheet2
-------------
You have listed the names and hours worked
in cols A and B, data from row2 down,
and want to compute the pay due in col C, viz.:

Emp_HoursWorked_PayDue
XYZ____8_________240
ABC___10________100
DEF___10________200

Put in C2:
=VLOOKUP(TRIM(A2),Sheet1!$A$2:$B$4,2,0)*B2
Copy C2 down to C4

C2:C4 will return the figures indicated in the table above

 

[Moshe Rosenberg]

Great! Now if I want to combine two employees rates in one cell how do I do
this? I tried:

=VLOOKUP(TRIM(A2),Sheet1!$A$2:$B$4,2,0)+(TRIM(A3),Sheet1!$A$2:$B$4,2,0)

but it did not work. Syntax is wrong.

How do I do this?

 

[Max]

Think you missed out the "VLOOKUP" in the 2nd part ..

This will add the rates for the
Emps XYZ (in Sheet2's A2) and ABC (in Sheet2's A3)
(if that's what you want)

=VLOOKUP(TRIM(A2),Sheet1!$A$2:$B$4,2,0)+VLOOKUP(TRIM(A3),Sheet1!$A$2:$B$4,2,
0)

The result returned will be 40 (i.e. 30+10)

 

[Moshe Rosenberg]

Okay - I figured it out:

=VLOOKUP(TRIM(A2),Sheet1!$A$2:$B$4,2,0)+VLOOKUP(TRIM(B2),Sheet1!$A$2:$B$4,2,0)

But what if B2 (the other employee) is not there? How do I avoid the #N/A
error since it's looking for something in B2?

 

[Frank Kabel]

Hi
then use a formula combination such as
=IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...))

 

[Max]

Try with error traps then, something like:

=IF(OR(ISNA(VLOOKUP(TRIM(A2),Sheet1!$A$2:$B$4,2,0)),ISNA(VLOOKUP(TRIM(B2),Sh
eet1!$A$2:$B$4,2,0))),"",VLOOKUP(TRIM(A2),Sheet1!$A$2:$B$4,2,0)+VLOOKUP(TRIM
(B2),Sheet1!$A$2:$B$4,2,0))

which will return blank ""
if either VLOOKUP for A2 or B2 returns #NA
(cannot find a match)

 

[Max]

=VLOOKUP(TRIM(A2),Sheet1!$A$2:$B$4,2,0)+VLOOKUP(TRIM(B2),Sheet1!$A$2:$B$4,2,
0)

But what if B2 (the other employee) is not there?
How do I avoid the #N/A error since it's looking
for something in B2?

Perhaps another way of looking at it is that you want
the combined rates of both emps in A2 and B2
but if either A2 or B2 is empty,
then you'll settle for the rate of the emp in the cell
which isn't empty

=IF(AND(A2="",B2=""),"",IF(AND(A2<>"",B2=""),VLOOKUP(TRIM(A2),Sheet1!$A$2:$B
$4,2,0),IF(AND(A2="",B2<>""),VLOOKUP(TRIM(B2),Sheet1!$A$2:$B$4,2,0),VLOOKUP(
TRIM(A2),Sheet1!$A$2:$B$4,2,0)+VLOOKUP(TRIM(B2),Sheet1!$A$2:$B$4,2,0))))

(Above assumes that the emps' names entered in
either A2 or B2 are found in the rates table in Sheet1,
i.e. no problem with matching)