Excel - How to Use a VLOOKUP Formula - Expert Solution

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.

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.