**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.*