Vlookup in multiples columns
 

I have the following simple spreadsheet. I'm tryping to use VLOOKUP to find
the rate of pay based on the Job Type and the Hourly Pay which refers to a
table below that. IE for Jones who's in Assembly, his hourly rate of pay at
Full-time rate would be 6.75. I've tried =VLOOKUP(B5,$A$14:$D$16,2), this
works for those who are in the first column (2-Fulltime) but not for the
hours. Where am I going wrong?

Employee Job Type Hrs. Worked Hourly Pay Rate Pay
Jones Assembly 5 2
Smith QC 3 4
Gray Sorter 7.5 3
Kline Assembly 2 2
Ominsky Assembly 2.5 4
Fulton Sorter 4 3
Clifford Sorter 3 2

Job Type 2-Full Time 3-Part Time 4-Overtime
Assembly $6.75 $5.75 $10.75
QC $7.00 $6.00 $11.00
Sorter $5.50 $5.00 $9.00

--
Trainer

Vlookup in multiples columns
 

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"

=VLOOKUP(B5,$A$14:$D$16,MATCH(D5&"",LEFT($A$13:$D$ 13,1),0),0)

OR with the second table headers modified as below

Job Type 2 3 4
Assembly $6.75 $5.75 $10.75
QC $7.00 $6.00 $11.00
Sorter $5.50 $5.00 $9.00

you could try the below non-array formula
=VLOOKUP(B5,$A$14:$D$16,MATCH(D5,$A$13:$D$13,1),0)


--
Jacob


"Trainer" wrote:

I have the following simple spreadsheet. I'm tryping to use VLOOKUP to find
the rate of pay based on the Job Type and the Hourly Pay which refers to a
table below that. IE for Jones who's in Assembly, his hourly rate of pay at
Full-time rate would be 6.75. I've tried =VLOOKUP(B5,$A$14:$D$16,2), this
works for those who are in the first column (2-Fulltime) but not for the
hours. Where am I going wrong?

Employee Job Type Hrs. Worked Hourly Pay Rate Pay
Jones Assembly 5 2
Smith QC 3 4
Gray Sorter 7.5 3
Kline Assembly 2 2
Ominsky Assembly 2.5 4
Fulton Sorter 4 3
Clifford Sorter 3 2

Job Type 2-Full Time 3-Part Time 4-Overtime
Assembly $6.75 $5.75 $10.75
QC $7.00 $6.00 $11.00
Sorter $5.50 $5.00 $9.00

--
Trainer