Simple VLOOKUP, but not working

[EmTBHC]

Hi,

I've made a simple VLOOKUP, and for the majority of cells it comes back with the correct value. However, for some it says N/A. What is weird is that those values I have looked up in the table_array, with the 'Find' tool, and they are there.

I've checked for spaces etc, and even tried to copy/paste the value into the table_array, and say 'Remove duplicates' to test if there are any spaces etc, but it then confirms it's identical by removing one of them, saying it is a duplicate, hence it is the exact same value. So why is it saying N/A error in the Vlookup?

Hope this make sense.

Looking forward to hearing from you.

[#2]

can you include the sheet and formulas. If not attached here then as a link to onedrive or dropbox or the like. It is really hard to guess all the possibilities. It could be formatting or if you defined the lookup to be exact match or not, or numerous other options.

[#3]

Hi @mtarler Thanks for your quick reply.

The formula I'm using is this =VLOOKUP(D2,Sheet1!A2:B391, 2, FALSE)

I appreciate this might be a silly questions, but I how to I attach a file in this conversation?

[#4]

@EmTBHC 

Could be your table array. Try this:

=VLOOKUP(D2,Sheet1!$A$2:$B$391, 2, FALSE)

[#5]

Hi Patrick, just tried, that sadly didn't work.

[#6]

out of curiosity what happens if you use:
=MATCH(D2,Sheet1!$A$2:$A$391,0)
also, can you post examples of values that are having issues (some common issues include confusion between " and “ (curly quote) or - and – (long dash) to name just a couple)
but the worksheet itself would be most helpful.

[#7]

@mtarler when I tried that formula, the output was 136. Even though I have no numbers in my spreadsheet. 

How do I attach the file to this conversation? Very much appreciate your help.

[#8]

136 is where in the list the matching item was found (so cell A137). Which mean excel is finding the matching item in the list.
as for attaching, new members don't have that ability so if you can't attach then you can use onedrive or dropbox or the like and give a link. or you can click my pfp and then select Message and you should be able to attach a file to a private message.

[#9]

Super, thank you, I'll try that now.

[#10 · Accepted solution]

@EmTBHC The problem is the lookup_range for all those cells keep changing and so some cells are looking up in ranges that don't have that value.  I have attached an updated version using dynamic arrays and only 1 array formula for the whole range:

=VLOOKUP(D2:D97,Sheet1!$A$2:$B$391, 2, FALSE)

if you don't have excel 365 then change the D2:D97 to only D2 and then fill down.  Since the D2 doesn't have any $ in front it will increment as it fills down but the range has those $ in front of the col and row so it will stay fixed when copied or filled.

This solution was also noted above but you said it didn't work for you but maybe you didn't understand the importance of those $

To "fill down" you can select the top cell and click the small box in the bottom right corner and drag down or select the whole range can click ctrl-D

[#11]

That's amazing! Thank you so much for taking the time to help me with this, really appreciate it!