Excel - How to Use a VLOOKUP Formula - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Hello Team, I have a sheet with several tabs, Tab1 extracts data from 3 other tabs using vlookup, where the data in these 3 tabs is changed each month. how can I make vlookup functions in Tab1 work automatically once I put the new monthly data in the other 3 tabs. For now, I have to write vlookup formulas in tab1 every month as it doesn't work otherwise. Another issue please, Mostly after applying Vloopkup I get some cells with #NA, when I write the same formula again in these cells it gives me the output as expected in some cells but also some few cells give me #NA & i have to write the formula again& again till all are completed.
Solved by S. D. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 26/09/2018 - 09:06
Welcome to excelchat!
Excelchat Expert 26/09/2018 - 09:07
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
User 26/09/2018 - 09:07
Excelchat Expert 26/09/2018 - 09:07
So, is it possible for you to show me the file?
User 26/09/2018 - 09:07
how much time would it take?
Excelchat Expert 26/09/2018 - 09:07
I'm not sure, I have to analyze the file.
User 26/09/2018 - 09:08
well.. first I ddin't know I would get a feedback now :)
User 26/09/2018 - 09:08
if u need the file I have to modify some things as i can't share it as it is
Excelchat Expert 26/09/2018 - 09:08
I understand.
Excelchat Expert 26/09/2018 - 09:09
Please make necessary changes and share the file.
User 26/09/2018 - 09:10
great , so can we postpne this lets say for 2 hours from now? or how is your policy for similar situations?
Excelchat Expert 26/09/2018 - 09:12
I'm afraid our policy is maximum 1 hour per session. If you think you can't continue this time, the chat will end automatically in 1 hour unless you choose to end it before. If you come back after 2 hours, you have to open a new chat
Excelchat Expert 26/09/2018 - 09:12
I wanted to see the file because I needed to see the formula's.
Excelchat Expert 26/09/2018 - 09:12
Which cells these formula's are referencing to.
Excelchat Expert 26/09/2018 - 09:13
How does the change of data affecting those formulas etc.
User 26/09/2018 - 09:13
yeah , sure ..
User 26/09/2018 - 09:14
well.. I have no option now :(
Excelchat Expert 26/09/2018 - 09:14
Okay, so shall we continue or you would like to end this now?
Excelchat Expert 26/09/2018 - 09:14
Oh, I'm sorry to hear that.
Excelchat Expert 26/09/2018 - 09:14
Umm, we can try without the file though.
User 26/09/2018 - 09:15
well.. ok, then that would be good ..
Excelchat Expert 26/09/2018 - 09:15
In that case it might take a lots of Q&A between two of us to figure out the problem.
User 26/09/2018 - 09:15
at least we may be able to get something
Excelchat Expert 26/09/2018 - 09:15
Yeah, sure.
Excelchat Expert 26/09/2018 - 09:15
For started, please share the formula you are using.
User 26/09/2018 - 09:15
i know .. sorry for that ..I thought I would have sometime before you get back to me :)
User 26/09/2018 - 09:16
ok, it's more than one of course but all are almost like this
User 26/09/2018 - 09:16
Excelchat Expert 26/09/2018 - 09:16
Well, I'm here and let's get this thing sorted.
User 26/09/2018 - 09:16
great thank you :)
Excelchat Expert 26/09/2018 - 09:16
What does the named range Name contain?
User 26/09/2018 - 09:17
User 26/09/2018 - 09:17
User 26/09/2018 - 09:18
the name range has normal text .. only 1 word & I make sure that no spaces are there
Excelchat Expert 26/09/2018 - 09:18
The look up ranges are always the same even after change of data?
User 26/09/2018 - 09:18
mm.. not sure I understand the question..
Excelchat Expert 26/09/2018 - 09:19
JulCap!A2:D135 this part of the formula is lookup range.
User 26/09/2018 - 09:19
Excelchat Expert 26/09/2018 - 09:19
This range changes when you paste new data for new month?
User 26/09/2018 - 09:20
yes.. but all the ordering of columns are the same only some values in some cells are changed each month ..
User 26/09/2018 - 09:21
but I copy the data from another sheet each month & then paste it in the JulCap tab
Excelchat Expert 26/09/2018 - 09:22
Do you delete the old columns before pasting new data?
User 26/09/2018 - 09:22
sometimes I do so & sometimes I just paste it above.. & it's the same results still
User 26/09/2018 - 09:22
I get the same issues I mean
User 26/09/2018 - 09:23
one note ..
User 26/09/2018 - 09:25
when I paste the data , I get a pop up window to browse to open a file , I cancel it & continue normally.
User 26/09/2018 - 09:25
not sure hy I get this window.. it pops up one I hit ctlr+V
Excelchat Expert 26/09/2018 - 09:26
When you paste new data and look at the formula, do you see any change in the formula?
User 26/09/2018 - 09:26
User 26/09/2018 - 09:27
it's the same, but only get me the #NA in some cells.. but some are updated normally
Excelchat Expert 26/09/2018 - 09:27
Okay, understood.
Excelchat Expert 26/09/2018 - 09:28
Unfortunately with these information it looks like everything is alright and you shouldn't get the N/A error.
Excelchat Expert 26/09/2018 - 09:29
And I am not able to figure our why you are getting those errors just from those information.
Excelchat Expert 26/09/2018 - 09:29
I'm sorry for the inconvenience, but I need to look at the source file itself.
User 26/09/2018 - 09:29
i c
User 26/09/2018 - 09:29
no prb ..
Excelchat Expert 26/09/2018 - 09:30
Do you have any other questions about this problem?
User 26/09/2018 - 09:30
not for this one obvously, as we're not able to move forward
User 26/09/2018 - 09:31
however is it possible that u may have an idea regarding the 2nd question?
User 26/09/2018 - 09:31
I thought this one would be an easy one :)
Excelchat Expert 26/09/2018 - 09:31
Yeah, I'm really sorry for the inconvenience. The 2nd question is?
User 26/09/2018 - 09:32
in 1 column, when I apply the vlookup I get some cells with #NA..Ifilter then & write down the formula again so I get the value normally for some yet some others give NA still ..
User 26/09/2018 - 09:32
& i keep filterin the NA & write the formula untill they are all retrieved
Excelchat Expert 26/09/2018 - 09:34
Not sure if it would help, but you can try this. On the Excel ribbon, go to the Formulas tab > Calculation group, click the Calculation Options button, and select Automatic
Excelchat Expert 26/09/2018 - 09:35
It seems to me those two are not for different problem, those are happening for the same reason.
User 26/09/2018 - 09:35
it already selected
User 26/09/2018 - 09:35
mm.. may be
User 26/09/2018 - 09:36
ok, then
Excelchat Expert 26/09/2018 - 09:36
If you could send me a few sample data, I could take a quick look.
User 26/09/2018 - 09:36
i'll try to do it as fast as I can & will see if I'm able to catch the time
Excelchat Expert 26/09/2018 - 09:37
We have 30 minutes remaining..
Excelchat Expert 26/09/2018 - 09:43
Please extend time again.
Excelchat Expert 26/09/2018 - 09:46
If you comeback and can't write in this box, don't be worred.

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
The Allstate Corporation
United Parcel Service
Dell Inc