SOLVED

vlookups and formatting issues

%3CLINGO-SUB%20id%3D%22lingo-sub-1779654%22%20slang%3D%22en-US%22%3Evlookups%20and%20formatting%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1779654%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%20I%20am%20working%20on%20vlookup%2C%20and%20I%20cannot%20seem%20to%20figure%20out%20the%20formatting%20for%20the%20table%20array%20file.%26nbsp%3B%20I%20am%20getting%20%23N%2FA%20all%20down%20the%20column.%26nbsp%3B%20I%20know%20my%20formula%20is%20correct%20bc%20when%20I%20type%20in%20the%20lookup%20value%20myself%20in%20the%20table%20array%2C%20the%20value%20shows%20up%20on%20the%20other%20sheet.%26nbsp%3B%20The%20lookup%20values%20here%20are%26nbsp%3B%20names.%26nbsp%3B%20Both%20files%20had%20the%20columns%20formatted%20as%20%22general.%22%26nbsp%3B%20Knowing%20%22text%20to%20column%22%20solves%20formatting%20issues%20with%20numbers%2C%20I%20formatted%20both%20sheets%20to%20have%20the%20column%20be%20%22text%22%20%26amp%3B%20did%20%22text%20to%20column%22%20to%20no%20avail.%26nbsp%3B%20I%20have%20also%20tried%20to%20copy%20the%20table%20array%20sheet%20%26amp%3B%20paste%20special%20values%20into%20a%20new%20spreadsheet.%26nbsp%3B%20This%20did%20not%20work%2C%20either.%26nbsp%3B%20What%20am%20I%20missing%3F%26nbsp%3B%20Your%20help%20is%20greatly%20appreciated!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1779654%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1779713%22%20slang%3D%22en-US%22%3ERe%3A%20vlookups%20and%20formatting%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1779713%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F832176%22%20target%3D%22_blank%22%3E%40MonicaMikol%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20possible%20for%20you%20to%20post%20a%20copy%20of%20(or%20an%20extract%20from)%20the%20actual%20spreadsheet%3F%20Without%20that%2C%20despite%20your%20quite%20thorough%20description%2C%20the%20best%20one%20could%20do%20is%20speculate%20wildly%20on%20what's%20going%20on.%20And%20I%20doubt%20that%20would%20be%20very%20helpful.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1779827%22%20slang%3D%22en-US%22%3ERe%3A%20vlookups%20and%20formatting%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1779827%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3EHi!%26nbsp%3B%20Yes%2C%20a%20very%20valid%20point!%26nbsp%3B%20Ok%2C%20so%20here%20is%20a%20sample%20of%20the%202%20sheets%20with%20which%20I%20am%20working.%26nbsp%3B%20In%20the%20%22origsheet%2C%22%20I%20left%20my%20formula%2C%20so%20you%20can%20see%20where%20the%20value%20was%20returned%2C%20once%20I%20typed%20the%20names%20in%20myself.%26nbsp%3B%20For%20the%20%22table%20array%22%20sheet%2C%20I%20needed%20to%20remove%20some%20columns%2C%20but%20I%20am%20trying%20to%20return%20the%20values%20from%20Column%20D.%26nbsp%3B%20Thanks%20in%20advance%20for%20your%20assistance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1781949%22%20slang%3D%22en-US%22%3ERe%3A%20vlookups%20and%20formatting%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1781949%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F832176%22%20target%3D%22_blank%22%3E%40MonicaMikol%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20see%20what%20your%20problem%20is.%20It%20this%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20see%20it%20there%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo.%3C%2FP%3E%3CP%3EAnd%20that%20illustrates%20the%20problem.%20And%20it's%20not%20in%20your%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20be%20more%20descriptive%3A%20There%20are%20SPACES%20after%20the%20names%20in%20the%20%22Table%20array%22%20file%2C%20the%20source%20file.%20And%20%3CSTRONG%3EVLOOKUP%3C%2FSTRONG%3E%20is%20looking%20for%20an%20exact%20match%20using%20names%20that%20DON'T%20have%20spaces%20following%20them.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20there%20are%20a%20number%20of%20ways%20to%20solve%20it.%20Perhaps%20the%20best%20would%20be%20to%20head%20it%20off%20at%20the%20source%2C%20if%20you%20can%20do%20that.%20Does%20that%20source%20data%20come%20from%20somebody%20else%3F%20Can%20you%20ask%20them%20to%20deliver%20%22clean%20data%22%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20second%20possibility%2C%20if%20you%20have%20the%20ability%20to%20modify%20that%20%22table%20array%22%20file%20yourself%3A%20the%20function%20TRIM%20will%20remove%20leading%20and%20trailing%20spaces%20from%20the%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet's%20start%20with%20those%20two%20possible%20fixes%2C%20with%20the%20first%20being%20the%20best%20by%20far....get%20clean%20data%20to%20start%20with.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20learning%20here%20vis-a-vis%20VLOOKUP%20and%20similar%20functions%20is%20that%26nbsp%3B%20they%20can%20often%20be%20messed%20up%20by%20%22invisible%22%20or%20nearly%20invisible%20distinctions.%20The%20computer%20sees%20those%20names%20as%20not%20matching%20because%20of%20the%20trailing%20spaces%3B%20that's%20a%20common%20cause%20of%20errors%20and%20easily%20overlooked%20because%20our%20eyes%20and%20brains%20can%20still%20see%20them%20as%20%22right%20answers%22%20and%20even%20overlook%20small%20things%20such%20as%20minor%20misspellings.%20To%20the%20computer%2C%20though%2C%20%22exact%20match%22%20means%20EXACT%20MATCH.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi! I am working on vlookup, and I cannot seem to figure out the formatting for the table array file.  I am getting #N/A all down the column.  I know my formula is correct bc when I type in the lookup value myself in the table array, the value shows up on the other sheet.  The lookup values here are  names.  Both files had the columns formatted as "general."  Knowing "text to column" solves formatting issues with numbers, I formatted both sheets to have the column be "text" & did "text to column" to no avail.  I have also tried to copy the table array sheet & paste special values into a new spreadsheet.  This did not work, either.  What am I missing?  Your help is greatly appreciated!!

4 Replies
Highlighted

@MonicaMikol 

 

Is it possible for you to post a copy of (or an extract from) the actual spreadsheet? Without that, despite your quite thorough description, the best one could do is speculate wildly on what's going on. And I doubt that would be very helpful.

Highlighted

@mathetesHi!  Yes, a very valid point!  Ok, so here is a sample of the 2 sheets with which I am working.  In the "origsheet," I left my formula, so you can see where the value was returned, once I typed the names in myself.  For the "table array" sheet, I needed to remove some columns, but I am trying to return the values from Column D.  Thanks in advance for your assistance!

Highlighted
Best Response confirmed by MonicaMikol (New Contributor)
Solution

 

@MonicaMikol 

 

I see what your problem is. It this

 

Can you see it there?

 

No.

And that illustrates the problem. And it's not in your formula.

 

Let me be more descriptive: There are SPACES after the names in the "Table array" file, the source file. And VLOOKUP is looking for an exact match using names that DON'T have spaces following them.

 

Now there are a number of ways to solve it. Perhaps the best would be to head it off at the source, if you can do that. Does that source data come from somebody else? Can you ask them to deliver "clean data"?

 

A second possibility, if you have the ability to modify that "table array" file yourself: the function TRIM will remove leading and trailing spaces from the column.

 

Let's start with those two possible fixes, with the first being the best by far....get clean data to start with.

 

The learning here vis-a-vis VLOOKUP and similar functions is that  they can often be messed up by "invisible" or nearly invisible distinctions. The computer sees those names as not matching because of the trailing spaces; that's a common cause of errors and easily overlooked because our eyes and brains can still see them as "right answers" and even overlook small things such as minor misspellings. To the computer, though, "exact match" means EXACT MATCH.

Highlighted
Thank you SO MUCH!!! I understand your explanation perfectly! I wasn't able to get "clean" data, but the TRIM function worked! From there, I just needed to manually check for names that were not an exact match (if we have the name as "Smith" but table array has "Smith, Jr.") I am definitely writing this down, so I have the information readily available should I come across this issue again. Thank you again so much for your time & your detailed explanation!!!