Question description:
This user has given permission to use the problem statement for this
blog.
I'm looking for a formula or format for if there is data in c4 on a sheet... It adds extra information underneath
Solved by X. E. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
09/07/2018 - 03:17
Hello, I hope you are having a good day. I am here to help with your question.
User
09/07/2018 - 03:18
Hi
User
09/07/2018 - 03:18
In a nutshell, If I was to put a client's name in lets say A3
User
09/07/2018 - 03:19
How can I run a code for B3 to provide me the address so then I don't have to write it out over and over
Excelchat Expert
09/07/2018 - 03:19
May I ask a few questions to confirm my understanding?
User
09/07/2018 - 03:20
Sure
Excelchat Expert
09/07/2018 - 03:20
do you have a table where you have a table with exhaustive information?
Excelchat Expert
09/07/2018 - 03:21
like clientname and addresses?
User
09/07/2018 - 03:21
Yes so I have a master copy where I keep all my client's details
User
09/07/2018 - 03:22
And I would like to transfer only some of the information onto a sheet for us to print out
Excelchat Expert
09/07/2018 - 03:23
so, can we design a vlookup to look for client's name in that list and populate address automatically in the next column?
User
09/07/2018 - 03:24
Yes that's what I'm after
Excelchat Expert
09/07/2018 - 03:24
there is a risk with linking formulas to another file. if you share this file or the file to which you link the formula is moved or renamed, you might end up with an error
Excelchat Expert
09/07/2018 - 03:25
I suggest that it is better to have a copy of the Master data in another sheet in the same file
Excelchat Expert
09/07/2018 - 03:25
let me demonstrate your requirement
Excelchat Expert
09/07/2018 - 03:25
in the sheet that we can see
User
09/07/2018 - 03:26
Okay
Excelchat Expert
09/07/2018 - 03:26
So, lets say sheet 2 has masterdata
Excelchat Expert
09/07/2018 - 03:26
we will design a formuua on sheet 1
Excelchat Expert
09/07/2018 - 03:27
so
Excelchat Expert
09/07/2018 - 03:27
now lets add a client name in A2
User
09/07/2018 - 03:28
What was the formula used? I missed it
Excelchat Expert
09/07/2018 - 03:28
so you see, with this formula, now you will have the address populated. Let me explain a little on how to use vlookup
Excelchat Expert
09/07/2018 - 03:29
Use VLOOKUP, one of the lookup and reference functions, when you need to find things in a table or a range by row.
Excelchat Expert
09/07/2018 - 03:29
=VLOOKUP(Value you want to look up, range where you want to lookup the value, the column number in the range containing the return value, Exact Match or Approximate Match – indicated as 0/FALSE or 1/TRUE)
Excelchat Expert
09/07/2018 - 03:29
have a look at this
Excelchat Expert
09/07/2018 - 03:29
I am looking up value in A2
Excelchat Expert
09/07/2018 - 03:30
in the table which is in Sheet2 column A to C
Excelchat Expert
09/07/2018 - 03:30
display details in column 2
Excelchat Expert
09/07/2018 - 03:30
FALSE looks for exact value of A2 in the table
Excelchat Expert
09/07/2018 - 03:30
TRUE looks for approximate value
Excelchat Expert
09/07/2018 - 03:31
please be aware that the value you are looking for (i.e A2) should be in the first column of the table in which you are looking for
Excelchat Expert
09/07/2018 - 03:31
Now Lets say you want to display contact number automatically
User
09/07/2018 - 03:31
Is this on Sheet 1 or 2 you're conducting the formula?
Excelchat Expert
09/07/2018 - 03:31
Sheet 1
Excelchat Expert
09/07/2018 - 03:32
to display contact number
Excelchat Expert
09/07/2018 - 03:32
I will lookup A2
Excelchat Expert
09/07/2018 - 03:32
in our Master data
Excelchat Expert
09/07/2018 - 03:32
we see that contact number is third column in our table
Excelchat Expert
09/07/2018 - 03:33
have exact match
Excelchat Expert
09/07/2018 - 03:33
so FALSE
Excelchat Expert
09/07/2018 - 03:33
now when you are using exact match if the client name is not in the Master data you will see #N/A
Excelchat Expert
09/07/2018 - 03:34
I will send a copy of this sheet for your reference and to play around to explore more
User
09/07/2018 - 03:34
Thank you
User
09/07/2018 - 03:34
Where will you be sending this file?
Excelchat Expert
09/07/2018 - 03:35
Here you are
[Uploaded an Excel file]
Excelchat Expert
09/07/2018 - 03:35
please feel free to explore more
Excelchat Expert
09/07/2018 - 03:35
this will help you a great deal with a lot of things
Excelchat Expert
09/07/2018 - 03:35
I hope I answered your question satisfactorily.
Excelchat Expert
09/07/2018 - 03:36
I would love to receive your feedback.
Excelchat Expert
09/07/2018 - 03:36
Have a fantastic day :)
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.
