Question description:
This user has given permission to use the problem statement for this
blog.
My VLOOKUP formula won't work when value the "lookup cell" is derived from a formula. If I manually transfer the formula derived value to a different cell, then VLOOKUP formula works fine. What's going on?
Solved by Z. J. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
08/06/2018 - 11:33
Hello there, Welcome to Got It Pro.
Excelchat Expert
08/06/2018 - 11:33
Hope you are having a good day.
Excelchat Expert
08/06/2018 - 11:33
Could you please upload the file you are having problems with? Please use the attachments button on the right of the chat tab.
Excelchat Expert
08/06/2018 - 11:37
Let me show you an example of how to use a Vlookup
User
08/06/2018 - 11:39
I am quite familiar with how to use Vlookup. Just can't figure out how to get around this problem. My excel file is over 50mb, so can't send it.
Excelchat Expert
08/06/2018 - 11:39
Alright, no problem
Excelchat Expert
08/06/2018 - 11:40
Could you please tell me what are you having problems with?
User
08/06/2018 - 11:40
As explained in my initial comments
Excelchat Expert
08/06/2018 - 11:41
I am sorry I did not get any initial comments.
Excelchat Expert
08/06/2018 - 11:41
[Uploaded an Excel file]
Excelchat Expert
08/06/2018 - 11:42
[Uploaded an Excel file]
User
08/06/2018 - 11:44
My VLOOKUP formula won't work when the value which the "lookup cell" is accessing is derived from a formula. If I manually transfer the formula derived value, for example the number 256) to a different cell, and then ask the VLOOKUP formula to look at this new cell, then the VLOOKUP formula works just fine. What's going on?
Excelchat Expert
08/06/2018 - 11:45
Alright, did you check if the formula works on another cell? Before the vlookup?
Excelchat Expert
08/06/2018 - 11:46
It's hard to say like this without having a look at what's going on.
Excelchat Expert
08/06/2018 - 11:47
Could you please copy some partial data here to show what are you trying to do?
Excelchat Expert
08/06/2018 - 11:49
Did you check if the lookup value is on the leftmost cell?
Excelchat Expert
08/06/2018 - 11:50
May I know which error are you getting?
Excelchat Expert
08/06/2018 - 11:50
#NA, #VALUE OR #REF
Excelchat Expert
08/06/2018 - 11:51
Are you still here? I'm sorry but I am not getting any response.
User
08/06/2018 - 11:52
Yes, I can tell the formula derived value to go to any cell I want, but the VLOOKUP function just won't work if that different cell is directly derived my formula or if electronically copied from orginal formula derived cell. But as long as I manually copy the numberic value into a new cell, then have VLOOKUP search that new cell, then the VLOOKUP function works fine.
User
08/06/2018 - 11:52
Yes, the error I get is #N/A
Excelchat Expert
08/06/2018 - 11:52
Well, I get what is happening.
Excelchat Expert
08/06/2018 - 11:53
But without looking at how things are working, its very hard to say what's wrong.
Excelchat Expert
08/06/2018 - 11:53
Well, let me tell you the reasons for an #N/A error.
Excelchat Expert
08/06/2018 - 11:54
Firstly, if there is a typo in the lookup value. I.E. if the formula is not correct.
Excelchat Expert
08/06/2018 - 11:55
Secondly, if you are assigning TRUE as the third parameter, as a approx. match but the lookup value is smaller than the smallest value in the list
Excelchat Expert
08/06/2018 - 11:55
*value in the lookup column.
Excelchat Expert
08/06/2018 - 11:55
Next, if you are looking for an exact match and the match is not found.
Excelchat Expert
08/06/2018 - 11:56
Or, the lookup column is not the leftmost column.
Excelchat Expert
08/06/2018 - 11:56
Another reason is if numbers are formatted as text.
Excelchat Expert
08/06/2018 - 11:57
Lastly if there are leading or trailing spaces.
Excelchat Expert
08/06/2018 - 11:57
Did you check for these?
User
08/06/2018 - 11:57
I know the formula is correct because it works fine if the lookup cell is simply a manually entered value
Excelchat Expert
08/06/2018 - 11:57
Then I guess it is a problem in the format
Excelchat Expert
08/06/2018 - 11:57
Please check if the format is correct and if there are extra spaces.
Excelchat Expert
08/06/2018 - 11:58
Not the VLOOKUP formula
Excelchat Expert
08/06/2018 - 11:58
The formula that the lookup value is derived from
Excelchat Expert
08/06/2018 - 11:58
Did you try it in another cell to see if the returned value is right?
Excelchat Expert
08/06/2018 - 11:59
What you can do is copy one or two rows here at the Excel Online panel on the middle of the screen.
Excelchat Expert
08/06/2018 - 11:59
There has to be a reason among these. These are the only reasons you get a #N/A error.
User
09/06/2018 - 12:00
Yes, all these were checked and don't apply
Excelchat Expert
09/06/2018 - 12:01
I am afraid it is not possible for me to diagnose the problem then.
Excelchat Expert
09/06/2018 - 12:02
Could you please modify the data and use a sample one and show me what you are trying to do.
Excelchat Expert
09/06/2018 - 12:02
Just make up some data.
Excelchat Expert
09/06/2018 - 12:02
Show me the formulas you are trying to use.
User
09/06/2018 - 12:08
Your excel screen seen by me is stuck and nothing I do can get it to accept entries, so will call it a lost cause with you for now.
Excelchat Expert
09/06/2018 - 12:09
I am sorry even I can't see your avatar.
Excelchat Expert
09/06/2018 - 12:09
I think this session is having issues.
Excelchat Expert
09/06/2018 - 12:09
I sincerely apologize for the inconvenience.
Excelchat Expert
09/06/2018 - 12:10
Please get another session. Another expert will surely provide you with the solution.
Excelchat Expert
09/06/2018 - 12:10
Thanks for using Got It Pro.
Excelchat Expert
09/06/2018 - 12:10
Have an outstanding day.
Excelchat Expert
09/06/2018 - 12:10
And I apologize again for the technical difficulties you had to endure.
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.
