Why won’t my VLookup formula work?

ebrandt

Board Regular
Joined
Dec 13, 2010
Messages
51
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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,559
Office Version
365
Platform
Windows
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.
 

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,611
Office Version
365, 2016
Platform
Windows
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
Code:
=VLOOKUP(CLEAN(A2),I7:J21,2,0)
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

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

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

or FIND and REPLACE
 
Last edited:

ebrandt

Board Regular
Joined
Dec 13, 2010
Messages
51
I've tried every suggestion above.................none have solved the problem.
 

Repush

Board Regular
Joined
Sep 21, 2015
Messages
133
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'
 
Last edited:

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,342
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

Board Regular
Joined
Dec 13, 2010
Messages
51
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,281
Messages
5,467,733
Members
406,549
Latest member
midcoastchris04

This Week's Hot Topics

Top