**Question description:**

*This user has given permission to use the problem statement for this
blog.*

I need to copy a VLOOKUP formula to a cell underneath but instead of moving the third value (B55) to (B56) which happens automatically, I want the formula to move from (B55) to (C55). Basically moving to the next column, not the next row. How do I make this happen?

Solved by O. U. in 33 mins

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

Excelchat Expert
03/09/2018 - 04:44

Hello, Welcome to Got it Pro.

User
03/09/2018 - 04:44

Hi, is anyone live?

Excelchat Expert
03/09/2018 - 04:45

I am here only

Excelchat Expert
03/09/2018 - 04:45

Can you share the sheet or provide me with sample data ?

Excelchat Expert
03/09/2018 - 04:45

You can add the sample data at the right side of the screen so that I can help you better.

User
03/09/2018 - 04:46

I want to copy one formula to the cell underneath. A VLOOKUP formula. The third value (B55) changes to (B56) atuomatically but I want it to change from B55 to C55 instead without having to manually change the formula. Is that possible?

Excelchat Expert
03/09/2018 - 04:47

I understand your query but I can assist you better if you can share the sheet or add the sample data

User
03/09/2018 - 04:47

The formula can be seen in the sheet,

User
03/09/2018 - 04:47

"Agents!B$55" should change to Agents!C$55

Excelchat Expert
03/09/2018 - 04:47

you formula is having references and I do not know what is written in those cells

Excelchat Expert
03/09/2018 - 04:48

yeah we can change it

Excelchat Expert
03/09/2018 - 04:48

Are you dragging the formula to down or to the right?

User
03/09/2018 - 04:48

Down :)

Excelchat Expert
03/09/2018 - 04:48

What is writte in B55.. is it column reference number?

User
03/09/2018 - 04:49

The formula is being dragged down, 55 in the last value changes to 56, but I want it to stay on 55 and instead B should change to C

User
03/09/2018 - 04:49

one sec

User
03/09/2018 - 04:49

It's a number

Excelchat Expert
03/09/2018 - 04:49

if you are dragging it down, excel will increase the row number, not the column number

User
03/09/2018 - 04:50

Alright, is there a way to change that? So excell changes the column number instead?

User
03/09/2018 - 04:50

By a formula or setting?

Excelchat Expert
03/09/2018 - 04:50

we can tackle this situation via creating some logic.

Excelchat Expert
03/09/2018 - 04:50

You have to forward me the sheet so that I can think of

Excelchat Expert
03/09/2018 - 04:50

There is not a direct way to do thi

Excelchat Expert
03/09/2018 - 04:50

this*

User
03/09/2018 - 04:50

So it's a big complicated then?

User
03/09/2018 - 04:51

a bit*

Excelchat Expert
03/09/2018 - 04:51

no, it is not

Excelchat Expert
03/09/2018 - 04:51

but right now you are referring to B55 for col reference

Excelchat Expert
03/09/2018 - 04:51

we can change the reference

Excelchat Expert
03/09/2018 - 04:51

it is just a number

User
03/09/2018 - 04:51

Alright, the thing is that the sheet contains confidential data

User
03/09/2018 - 04:52

So I can not share the whole sheet :(

Excelchat Expert
03/09/2018 - 04:52

so instead of changing B55 to C55 we can make it work with B55 to B56 and if you are unable to do that within this then same thing we can achieve via using different cell references

Excelchat Expert
03/09/2018 - 04:52

Add some data here

Excelchat Expert
03/09/2018 - 04:52

I need to understand your sheet and data how is it arranged

User
03/09/2018 - 04:52

No, that's exactly What I want :) I want to change from B55 to C55 not the other way around

User
03/09/2018 - 04:53

Ah ok

User
03/09/2018 - 04:53

What data do you need?

Excelchat Expert
03/09/2018 - 04:54

See changing B55 to C55 isnt your problem, it is just a number reference to vlookup so that you can get the right result. if you want to drag the formula down and want to change the reference from rows, we have to change the cell references so that you can get the desire result.

Excelchat Expert
03/09/2018 - 04:54

Look at the sheet at the right

Excelchat Expert
03/09/2018 - 04:54

I am adding some sample data

User
03/09/2018 - 04:54

Right

User
03/09/2018 - 04:55

How much does this service cost if I want to subscribe?

User
03/09/2018 - 04:55

Still watching by the way hehe

Excelchat Expert
03/09/2018 - 04:56

Let me solve the problem and then I will help you understand that as well.

Excelchat Expert
03/09/2018 - 04:56

:)

User
03/09/2018 - 04:56

okey

Excelchat Expert
03/09/2018 - 04:56

So there is a table you can see with Name ID and Values

User
03/09/2018 - 04:56

yep

Excelchat Expert
03/09/2018 - 04:56

I am trying to look up names and return ID

Excelchat Expert
03/09/2018 - 04:56

H2 is working fine and returning ID

Excelchat Expert
03/09/2018 - 04:57

if I drag this down it will result the ID for C

Excelchat Expert
03/09/2018 - 04:57

which is correct

User
03/09/2018 - 04:58

Yep, Gotcha so far :)

Excelchat Expert
03/09/2018 - 04:58

but in your query you want to change the reference from B55 to B56 which means in the example to get the Value of A in H3 and not the ID of C (Changing the column reference)

User
03/09/2018 - 04:58

No I want it to change to C55

User
03/09/2018 - 04:59

The value I want to return exists in a row moving to the right

User
03/09/2018 - 04:59

If that makes sense

Excelchat Expert
03/09/2018 - 05:00

Yeah that is what I am saying... you are dragging down but you do not want B55 to B56 but C55 which is equivalent in our example to not the ID of C but the value of A in H3, right?

Excelchat Expert
03/09/2018 - 05:00

think yellow highlighted cell as B55

User
03/09/2018 - 05:01

okay

Excelchat Expert
03/09/2018 - 05:02

you understood what I am trying to say?

User
03/09/2018 - 05:02

One second, let me read what you said again

User
03/09/2018 - 05:02

Sorry I am immensely stupid right now, at least feel like so

Excelchat Expert
03/09/2018 - 05:03

if you have shared the sheet with some sample data, it would be easy for you to understand. because I am trying to explain you the B55 and B56 in different cells here

Excelchat Expert
03/09/2018 - 05:03

:)

User
03/09/2018 - 05:03

Okay, let me see if I can share it

Excelchat Expert
03/09/2018 - 05:04

your B55, B56 is just returning the reference number to vlookup.

User
03/09/2018 - 05:09

Woa,

Excelchat Expert
03/09/2018 - 05:09

?

User
03/09/2018 - 05:09

My colleague solved it for me like this:

User
03/09/2018 - 05:09

=OFFSET(Test!$A$6,0,ROW(A1)-1)

User
03/09/2018 - 05:09

If I add this formula instead of the last value B55

User
03/09/2018 - 05:09

It will change one step to the right (C55)

Excelchat Expert
03/09/2018 - 05:10

There are many ways to solve.

User
03/09/2018 - 05:10

Is it correct?

User
03/09/2018 - 05:10

Right! Is your was simplier?

Excelchat Expert
03/09/2018 - 05:10

Wait, let me explain this to you

User
03/09/2018 - 05:10

okay

Excelchat Expert
03/09/2018 - 05:11

OFFSET(Test!$A$6,0,ROW(A1)-1)

Excelchat Expert
03/09/2018 - 05:11

In this row function will return the row number

Excelchat Expert
03/09/2018 - 05:11

then it will subtract 1 from it

Excelchat Expert
03/09/2018 - 05:12

like row (A1) =1, Row (C1)=3

Excelchat Expert
03/09/2018 - 05:12

got it?

Excelchat Expert
03/09/2018 - 05:12

so if you are using this formula in say, D14 cell... Row =14-1 =13

Excelchat Expert
03/09/2018 - 05:13

So, OFFSET(Test!$A$6,0,ROW(A1)-1) will become OFFSET(Test!$A$6,0,14)

User
03/09/2018 - 05:13

Yeah

User
03/09/2018 - 05:13

This was great help

User
03/09/2018 - 05:13

Didn't know this service existed

Excelchat Expert
03/09/2018 - 05:14

:)

User
03/09/2018 - 05:14

Are you associated with Microsoft?

User
03/09/2018 - 05:14

Or independant?

Excelchat Expert
03/09/2018 - 05:14

Anyways, you got the solution?

User
03/09/2018 - 05:14

independent*

Excelchat Expert
03/09/2018 - 05:14

We are Excel experts working as independent freelancer

Excelchat Expert
03/09/2018 - 05:15

Got it pro is a platform that connects us

User
03/09/2018 - 05:15

Yeah, is there a way to save this chat/sheet In case I need to look at it in the future?

User
03/09/2018 - 05:15

Ah right

User
03/09/2018 - 05:15

Cost of this service?

Excelchat Expert
03/09/2018 - 05:15

You can access the chat later on also with your account

User
03/09/2018 - 05:16

Oh ok perfect

Excelchat Expert
03/09/2018 - 05:16

Let me send the link

User
03/09/2018 - 05:16

So you get paid for this chat? even if it was free for me? I hope so

Excelchat Expert
03/09/2018 - 05:16

https://www.excelchat.co/pricing/personal

Excelchat Expert
03/09/2018 - 05:16

Business model is altogether different

Excelchat Expert
03/09/2018 - 05:17

Did it solve your query?

User
03/09/2018 - 05:17

Yeah

Excelchat Expert
03/09/2018 - 05:17

Please end the session and provide a 5 star rating. I will also provide a 5 star rating for you so that you get the best expert next time.

Excelchat Expert
03/09/2018 - 05:17

Thanks for using Got it Pro. Looking forward to help you further.
Appreciate it :)

Excelchat Expert
03/09/2018 - 05:17

Have a good day, nice meeting you.

User
03/09/2018 - 05:17

Thanks man or Lady

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