**Question description:**

*This user has given permission to use the problem statement for this blog.*

Use the Vlookup Function to complete the "employee" column of table 2. Use "job Id" from table 2 as your lookup_value(s) and table 1 as your reference.

Solved by C. H. in 16 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
26/05/2018 - 05:53

Hi

Excelchat Expert
26/05/2018 - 05:53

Welcome to Got it Pro

User
26/05/2018 - 05:53

Hi I am a student and trying to figure out the problem

User
26/05/2018 - 05:53

It will not let me up load it

User
26/05/2018 - 05:54

Use the Vlookup Function to complete the "employee" column of table 2. Use "job Id" from table 2 as your lookup_value(s) and table 1 as your reference.

Excelchat Expert
26/05/2018 - 05:54

I will need to see the file to help you out

User
26/05/2018 - 05:54

I just do not know how to set up the formula

Excelchat Expert
26/05/2018 - 05:54

Can i show a sample data on the spreadsheet on the right to help you out?

User
26/05/2018 - 05:55

can you see that

Excelchat Expert
26/05/2018 - 05:55

Yes

Excelchat Expert
26/05/2018 - 05:56

Please tell more

User
26/05/2018 - 05:56

So E 50 is what I try to do, but I think it is wrong

Excelchat Expert
26/05/2018 - 05:56

No formula in E50

Excelchat Expert
26/05/2018 - 05:56

Can you share the excel?

User
26/05/2018 - 05:57

Well I copied and pasted it and it wont let me for some reason

Excelchat Expert
26/05/2018 - 05:57

ohk

Excelchat Expert
26/05/2018 - 05:57

But e50 does not show formula

Excelchat Expert
26/05/2018 - 05:58

will need data from other sheet as well

User
26/05/2018 - 05:58

That is the spread sheet

User
26/05/2018 - 05:58

and what I am suppose to do is
Employee Wage Information Bonus Information
Employee Jim Jill Jake Jennifer Time 10%
Hourly Wage $52 $50 $47 $48 Outcome 10%
Table 1 - Painting Projects
Job ID Estimated Hours Employee Hours Worked Hourly Wage Difficulty Quality Time Bonus? Outcome Bonus? Job Pay Time Bonus $ Outcome Bonus $ Total Pay Comments
1 50 Jim 54 $52 1 3 FALSE TRUE $2,808 $0 $281 $3,089 Good Job
2 40 Jill 40 $50 5 2 FALSE TRUE $2,000 $0 $200 $2,200 Too Much Time
3 80 Jim 86 $52 4 3 FALSE TRUE $4,472 $0 $447 $4,919 Too Much Time
4 40 Jake 42 $47 1 2 FALSE FALSE $1,974 $0 $0 $1,974 Good Job
5 80 Jim 78 $52 3 1 FALSE FALSE $4,056 $0 $0 $4,056 Poor Quality
6 40 Jake 38 $47 3 3 TRUE TRUE $1,786 $179 $179 $2,143 Too Much Time
7 50 Jim 51 $52 3 3 FALSE TRUE $2,652 $0 $265 $2,917 Good Job
8 40 Jennifer 36 $48 3 1 FALSE FALSE $1,728 $0 $0 $1,728 Poor Quality
9 50 Jim 48 $52 3 2 TRUE FALSE $2,496 $250 $0 $2,746 Too Much Time
10 40 Jill 42 $50 2 2 FALSE FALSE $2,100 $0 $0 $2,100 Too Much Time
11 70 Jake 74 $47 2 1 FALSE FALSE $3,478 $0 $0 $3,478 Too Much Time
12 50 Jennifer 51 $48 2 1 FALSE FALSE $2,448 $0 $0 $2,448 Poor Quality
13 70 Jennifer 70 $48 2 1 FALSE FALSE $3,360 $0 $0 $3,360 Poor Quality
14 60 Jake 58 $47 2 2 TRUE FALSE $2,726 $273 $0 $2,999 Good Job
15 70 Jake 66 $47 3 3 TRUE TRUE $3,102 $310 $310 $3,722 Good Job
16 50 Jill 48 $50 1 1 FALSE FALSE $2,400 $0 $0 $2,400 Poor Quality
17 50 Jennifer 47 $48 1 2 TRUE FALSE $2,256 $226 $0 $2,482 Too Much Time
18 60 Jill 65 $50 2 3 FALSE TRUE $3,250 $0 $325 $3,575 Too Much Time
19 60 Jill 61 $50 2 3 FALSE TRUE $3,050 $0 $305 $3,355 Good Job
20 80 Jennifer 79 $48 3 1 FALSE FALSE $3,792 $0 $0 $3,792 Poor Quality
Table 3 - Employee Summary
Table 2 - Touchup Projects Employee # of Jobs Total Hours Total Pay # of Touch-ups Cost Touch-ups Average Cost/Job True Cost/Hour
Job ID Employee Difficulty Cost Jim 1 $400 #DIV/0! #DIV/0!
5 5 $400 Jill 1 $250 #DIV/0! #DIV/0!
8 8 $600 Jake 1 $300 #DIV/0! #DIV/0!
11 11 $300 Jennifer 4 $2,300 #DIV/0! #DIV/0!
12 12 $700
13 13 $500 Table 4 - Project Summary by Difficulty
16 16 $250 Difficulty # of Jobs Total Pay # of Touch-ups Cost Touch-ups Total Cost Average Cost/Job Average Hours/Job
20 20 $500 1 4 $9,944 $9,944 $2,486
2 7 $21,315 $21,315 $3,045
3 7 $21,104 $21,104 $3,015
4 1 $4,919 $4,919 $4,919
5 1 $2,200 $2,200 $2,200

User
26/05/2018 - 05:58

sorry hold on

Excelchat Expert
26/05/2018 - 05:58

paste data on sheet 2

User
26/05/2018 - 05:59

Use the Vlookup Function to complete the "employee" column of table 2. Use "job Id" from table 2 as your lookup_value(s) and table 1 as your reference.

User
26/05/2018 - 05:59

What data?

Excelchat Expert
26/05/2018 - 05:59

paste data on sheet 2

Excelchat Expert
26/05/2018 - 05:59

ok let me see

Excelchat Expert
26/05/2018 - 05:59

you need formula in cell b31?

User
26/05/2018 - 06:00

yes

Excelchat Expert
26/05/2018 - 06:00

See cell B31 now

Excelchat Expert
26/05/2018 - 06:01

Does it help?

Excelchat Expert
26/05/2018 - 06:01

Please let me know

Excelchat Expert
26/05/2018 - 06:01

You removed the formula in B31?

User
26/05/2018 - 06:01

Yes so why do I only do to C for the table array

Excelchat Expert
26/05/2018 - 06:02

It is because the emplyee name is in C and emplyee id is in A

Excelchat Expert
26/05/2018 - 06:02

i mean job id is in A

Excelchat Expert
26/05/2018 - 06:02

So our range is from A to C

Excelchat Expert
26/05/2018 - 06:02

3 denotes the 3rd column which is C in our range that we selected

Excelchat Expert
26/05/2018 - 06:03

from A to C, 3 denotes C which is emplyee name

Excelchat Expert
26/05/2018 - 06:03

Hence till C

Excelchat Expert
26/05/2018 - 06:03

Do you understand?

Excelchat Expert
26/05/2018 - 06:03

Please let me know

User
26/05/2018 - 06:03

Oh well that was what was getting me because it just said ref the table which I thought it was the whole thing

Excelchat Expert
26/05/2018 - 06:04

So now you understand what i told you?

Excelchat Expert
26/05/2018 - 06:04

Does that help?

User
26/05/2018 - 06:04

yes, that helps and why did you put the 0 at the end

Excelchat Expert
26/05/2018 - 06:04

0 means exact match

Excelchat Expert
26/05/2018 - 06:04

against the matching value emplyee name will be shown

Excelchat Expert
26/05/2018 - 06:05

Hope it helps

Excelchat Expert
26/05/2018 - 06:05

Please do give good rating and reviews..it really helps

User
26/05/2018 - 06:05

Oh okay, and if I was doing the VLookup for the difficulty

Excelchat Expert
26/05/2018 - 06:05

We have a policy of one question per session

User
26/05/2018 - 06:06

would I do A-F

Excelchat Expert
26/05/2018 - 06:06

Hope you understand

Excelchat Expert
26/05/2018 - 06:06

Yes exactly

Excelchat Expert
26/05/2018 - 06:06

You are absolutely correct

User
26/05/2018 - 06:06

okay, perfect thank you so much!!

Excelchat Expert
26/05/2018 - 06:06

Make the formula i would help you with it

Excelchat Expert
26/05/2018 - 06:06

or do you want me to make the formula?

User
26/05/2018 - 06:07

=VLOOKUP(A34,$A$6:$F$26,6,0)

Excelchat Expert
26/05/2018 - 06:07

Yes

Excelchat Expert
26/05/2018 - 06:07

Perfect

Excelchat Expert
26/05/2018 - 06:08

Please do give good ratings when you end the session

User
26/05/2018 - 06:08

I will!!

Excelchat Expert
26/05/2018 - 06:08

Have a great day

User
26/05/2018 - 06:08

You too

Excelchat Expert
26/05/2018 - 06:08

Take Care

Excelchat Expert
26/05/2018 - 06:08

You can provide your rating when you end the session

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*