Why won’t my VLookup formula work?

[ebrandt]

I have successfully used the VLookup formulas for years. It is one of my go to formulas in Excel.

I have a situation now that I can’t figure out. I am importing a list of codes from an outside source into my Excel workbook and trying to match them up with identical codes that already reside in my workbook and return the value of the cell to the right.

Although the values of the codes appear to be identical, Excel will not recognize them. I have also tried the Match function with the same result.

Am I missing something regarding the data from the outside source? Does it need to be reformatted in some way I am missing? I have tried lots of changes to the formatting of the data and checked to make sure there are no extra spaces to no avail.

Here is what a typical code looks like………..”01 45 23 - Material Testing and Special Inspections”

The Vlookup formula returns #N/A

 

[Norie]

As well as spaces have you checked for other non-printing characters?

For example CHAR(160) is quite common when data is sourced from the web.

 

[Marcelo Branco]

Try the macro TrimALL to clean up your data
http://dmcritchie.mvps.org/excel/join.htm#trimall

M.

 

[Scott T]

the data from the outside source may have non-printable characters like non breaking space or carriage return. Try using CLEAN to remove any non-printable characters.


something like

=VLOOKUP(CLEAN(A2),I7:J21,2,0)

 

[jtakw]

Hi,

CLEAN does not work for "non-breaking" spaces...

Try: =SUBSTITUTE(A1,CHAR(160)," ")

or FIND and REPLACE

 

[ebrandt]

I've tried every suggestion above.................none have solved the problem.

 

[Repush]

Why don't you place your set of codes in a sheet, append the new codes to te bottom of this and then apply a filter (advanced) ticking 'copy to . .' and 'Unique values'

 

[Special-K99]

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

 

[ebrandt]

Thanks for all of the suggestions! I finally figured it out.

The problem was with the original data that I was trying to match to. I remembered that it also came from an online app, and when I went back to it and used the Substitute formula to get rid of CHAR(160), my Vlookup worked.