< Go Back

Excel - COLUMN Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I have 2 sheets. One sheet with the demographics of contractors. eg. Company A, Hispanic, Male. In the second sheet I would like to be able to choose the company from a drop down menu (listing all companies) in column A and have columns B and C automatically populated with Hispanic and Male.

Solved by A. Y. in 34 mins

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

Excelchat Expert 16/10/2018 - 05:45
Welcome to Excelchat
User 16/10/2018 - 05:45
Hi thank you.
Excelchat Expert 16/10/2018 - 05:45
I see that your question is about populating list automatically..
User 16/10/2018 - 05:46
yes.
Excelchat Expert 16/10/2018 - 05:46
Can you upload your data in here.? So I can better assist you..
User 16/10/2018 - 05:46
Ok. it won't be saved after we end chat correct?
User 16/10/2018 - 05:47
[Uploaded an Excel file]
Excelchat Expert 16/10/2018 - 05:47
I think you can see the history of this session for later ..
User 16/10/2018 - 05:48
I'd like when I enter something in Column A of Sheet 2, columns B and C auto populate based on what's in demographics
Excelchat Expert 16/10/2018 - 05:51
so do you want to populate Prime Contractor SBE (SBE/Non-SBE), Prime Contractor Ethnicity on based on Prime Contractor in column A..
User 16/10/2018 - 05:52
yep!
User 16/10/2018 - 05:52
as well as gender
Excelchat Expert 16/10/2018 - 05:52
for column B .. you have SBE and Non-SBE...
Excelchat Expert 16/10/2018 - 05:52
do you want to display Y or N ?
User 16/10/2018 - 05:53
instead of Y it should have been SBE and Non-SBE for N
Excelchat Expert 16/10/2018 - 05:53
ok..
Excelchat Expert 16/10/2018 - 05:53
I can help you with that..
Excelchat Expert 16/10/2018 - 05:53
hold please.. please...
Excelchat Expert 16/10/2018 - 05:53
it will take a little bit of time..
User 16/10/2018 - 05:54
no problem, i'm at your mercy lol
Excelchat Expert 16/10/2018 - 05:54
ok..
Excelchat Expert 16/10/2018 - 06:00
do you want to autopopulate for sub-contractors also?
User 16/10/2018 - 06:00
yes
Excelchat Expert 16/10/2018 - 06:00
one quick question..
Excelchat Expert 16/10/2018 - 06:00
AAA Automated Door Repair, Inc.
Excelchat Expert 16/10/2018 - 06:00
E4
Excelchat Expert 16/10/2018 - 06:00
sub-ctonractor..
Excelchat Expert 16/10/2018 - 06:01
you have data in the table..
Excelchat Expert 16/10/2018 - 06:01
but that subcontractor data is not in Demographics..
User 16/10/2018 - 06:01
yeah that's old data
Excelchat Expert 16/10/2018 - 06:01
ok..
Excelchat Expert 16/10/2018 - 06:02
hold on please..
Excelchat Expert 16/10/2018 - 06:05
it looks like file size increase to 51M..
Excelchat Expert 16/10/2018 - 06:05
so I can upload it in here..
Excelchat Expert 16/10/2018 - 06:05
hold on please..
User 16/10/2018 - 06:08
ok
Excelchat Expert 16/10/2018 - 06:09
Hold on please.. I am resizing the file..
Excelchat Expert 16/10/2018 - 06:13
[Uploaded an Excel file]
Excelchat Expert 16/10/2018 - 06:13
Please check the file..
Excelchat Expert 16/10/2018 - 06:13
column B to D, F to H will auto be populated based on contractor's name..
User 16/10/2018 - 06:14
Awesome! Could you tell me how you did it more or less?
Excelchat Expert 16/10/2018 - 06:14
of cause..
Excelchat Expert 16/10/2018 - 06:15
Since you have table in the sheet2..
Excelchat Expert 16/10/2018 - 06:15
I added vlookup formula int the cell...
Excelchat Expert 16/10/2018 - 06:15
=IFERROR(VLOOKUP([@[Prime Contractor]],Table1,2,0),"")
Excelchat Expert 16/10/2018 - 06:16
IFERROR is just get rid of error message in case of vlookup can't find data..
Excelchat Expert 16/10/2018 - 06:16
so main formula is
Excelchat Expert 16/10/2018 - 06:16
VLOOKUP([@[Prime Contractor]],Table1,2,0)
Excelchat Expert 16/10/2018 - 06:17
[@[Prime Contractor]] is contractor name
Excelchat Expert 16/10/2018 - 06:17
Table1.. is Demographics table name..
Excelchat Expert 16/10/2018 - 06:17
and it is searching 2nd column on Demographics table..
User 16/10/2018 - 06:18
Did you have to name the table and columns as a separate step?
Excelchat Expert 16/10/2018 - 06:18
no..
Excelchat Expert 16/10/2018 - 06:18
table is already there..
User 16/10/2018 - 06:18
Ok understood! Thank you so much :)
Excelchat Expert 16/10/2018 - 06:18
no problem..

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 privacy is guaranteed. Your session will not be used for blog unless you give us persmission.