Expert Solution - Excel COLUMN Problems

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

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