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
Yes, I have read it.
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
Does this solve your query?
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
Please check now
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
please check what number format is your formula cell
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
Is there anything else I can help you with this query?
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
I didn't get your question
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.
