All solutions COLUMNS Expert Solution – Excel COLUMN Problems

Excel - COLUMN Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc