# Excel - COLUMN Function Problem - Expert Solution

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

I have an excel sheet with two different pages. On the first there is a row with client names. On the second is a variety of columns, including client names and a percentage number for each client. What I want is to create a column on the first page that identifies the name on sheet one, finds it on sheet two, and then copies the number on sheet two onto the new column on sheet one.
Solved by V. D. in 29 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 17/05/2018 - 06:10
Hi…Welcome to Got It Pro
User 17/05/2018 - 06:11
Hi, have you had a chance to read what my problem is?
Excelchat Expert 17/05/2018 - 06:11
Excelchat Expert 17/05/2018 - 06:11
Do you have file to share of data to illustrate in this blank sheet?
User 17/05/2018 - 06:12
I can recreate what it looks like, but i cannot share it due to private information on it
Excelchat Expert 17/05/2018 - 06:12
sure, please recreate in this blank sheet
Excelchat Expert 17/05/2018 - 06:12
There is Sheet1 and Sheet2
User 17/05/2018 - 06:13
There we go
Excelchat Expert 17/05/2018 - 06:14
So you want ROA to be brought into Sheet1
User 17/05/2018 - 06:14
Yes
Excelchat Expert 17/05/2018 - 06:15
ok...this needs to be a lookup formula, I am creating it now
User 17/05/2018 - 06:15
basically in my mind the formula would be "look at cell B2 on sheet one, and find the matching name in column D on sheet two, then find the corresponding ROA number in column H of sheet two, and copy that to sheet 1
Excelchat Expert 17/05/2018 - 06:16
Exactly
Excelchat Expert 17/05/2018 - 06:16
The Index-Match lookup function is doing exactly what you have just stated
Excelchat Expert 17/05/2018 - 06:17
User 17/05/2018 - 06:18
Let me try it on my own sheet quickly
Excelchat Expert 17/05/2018 - 06:18
sure
User 17/05/2018 - 06:20
For the number 8 i would count how many columns in the ROA column is correct?
Excelchat Expert 17/05/2018 - 06:20
Yes
Excelchat Expert 17/05/2018 - 06:21
It depends on which columns you are indexing
User 17/05/2018 - 06:21
I am having problems, so I am going to copy over some information
Excelchat Expert 17/05/2018 - 06:22
Like i have column \$A:\$H that I am indexing, and column H is 8th column
Excelchat Expert 17/05/2018 - 06:23
Is it column J that you have ROA on?
User 17/05/2018 - 06:23
Ok this is now what my sheet looks like
User 17/05/2018 - 06:23
Yes, column J is the output column on the "Bob" Sheet
Excelchat Expert 17/05/2018 - 06:24
ok
Excelchat Expert 17/05/2018 - 06:24
will follow the same logic but with a change in cell reference
User 17/05/2018 - 06:24
Thank you
Excelchat Expert 17/05/2018 - 06:25
User 17/05/2018 - 06:26
I believe the 10 should be changed to 19. We want column S from RROA Sheet 2
User 17/05/2018 - 06:26
in the formula that is
Excelchat Expert 17/05/2018 - 06:26
ok...now if you want column S, then you will need to extend the table that you are indexing
Excelchat Expert 17/05/2018 - 06:27
please look at the formula in Bob sheet
Excelchat Expert 17/05/2018 - 06:27
I will tell you how to do it
Excelchat Expert 17/05/2018 - 06:27
currently it has 'RROA Sheet 2'!\$A:\$J
User 17/05/2018 - 06:27
Ok go on
Excelchat Expert 17/05/2018 - 06:27
please change that to 'RROA Sheet 2'!\$A:\$X
User 17/05/2018 - 06:28
so does that need to be \$A:\$S?
User 17/05/2018 - 06:28
oh X, to include the whole sheet?
Excelchat Expert 17/05/2018 - 06:28
this will cover all the columns in your table
Excelchat Expert 17/05/2018 - 06:28
Yes
User 17/05/2018 - 06:28
and then change the 10 to 19?
Excelchat Expert 17/05/2018 - 06:28
then the 10 needs to change to 19
Excelchat Expert 17/05/2018 - 06:28
perfect!
User 17/05/2018 - 06:29
Ok I have copied that onto my sheet and only made those two changes.
User 17/05/2018 - 06:29
When I hit enter, it just shows me the formula (with the equal sign at the start
Excelchat Expert 17/05/2018 - 06:30
As you can see its working on here
Excelchat Expert 17/05/2018 - 06:31
Does your actual file have the same sheet name that you have used here and exact cell references?
User 17/05/2018 - 06:31
Would it be because RROA sheet 2 is 1853 rows long and i need to account for that
User 17/05/2018 - 06:31
Yes it does
Excelchat Expert 17/05/2018 - 06:31
no...we have taken the whole column
Excelchat Expert 17/05/2018 - 06:31
we are not specifying any specific row numbres
User 17/05/2018 - 06:32
Ok
Excelchat Expert 17/05/2018 - 06:32
Excelchat Expert 17/05/2018 - 06:33
its possible that it is formatted as text
User 17/05/2018 - 06:33
It is. How should it be formatted?
User 17/05/2018 - 06:34
I changed it to number and it now works
Excelchat Expert 17/05/2018 - 06:34
You want a percentage, so format it as percentage
Excelchat Expert 17/05/2018 - 06:34
yep, formula cell should never be formatted as text
User 17/05/2018 - 06:34
That makes sense. I have one further question. We have some entries on RROA Sheet 2 such as what I will type...
Excelchat Expert 17/05/2018 - 06:34
User 17/05/2018 - 06:35
How will that alter the formula? Will it return .78, .79, or an error?
Excelchat Expert 17/05/2018 - 06:36
User 17/05/2018 - 06:36
Look on "RROA Sheet 2"
Excelchat Expert 17/05/2018 - 06:37
yes, i am looking at it
User 17/05/2018 - 06:37
sometimes on this sheet we will have two entries for the same client name. The main difference will be the number in column s, which is the reference column
Excelchat Expert 17/05/2018 - 06:37
It will return the first occurring value from column S
User 17/05/2018 - 06:37
how will the output on "Bob" be altered because there are two cells on "RROA sheet 2" that are in column S that the formula could pull from
Excelchat Expert 17/05/2018 - 06:38
So here in this case 0.78 as that's the first entry
User 17/05/2018 - 06:38
So it will always pull the number from the first entry from the top that matches the criteria of the formula
User 17/05/2018 - 06:38
?
Excelchat Expert 17/05/2018 - 06:39
That's correct
User 17/05/2018 - 06:39
Perfect! Then that is all I need
Excelchat Expert 17/05/2018 - 06:39
Please do give me a good feedback for my service.
Excelchat Expert 17/05/2018 - 06:39
Thanks for your time. Please do come back for any new question. Have a great day ahead!
User 17/05/2018 - 06:40
You as well. Thank you so much for your help!

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.