Excel - How to Use a VLOOKUP Formula - Expert Solution

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.

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.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc