Vlookup in multiples columns

T

Trainer

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
 
J

Jacob Skaria

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Vlookup 1
IF _Different Values- 1
Generating a cut list 11
Problem w/vlookup 5
IF 1
Sales with quarter and annual totals 1
VLOOKUP and multiple columns 6
vlookup question 6

Top