IFERROR VLOOKUP returning #N/A only when lookup value is hard keyed/pasted from another document

[MDlax30]

Hey guys,

Stumped on this one and have tried everything I personally can think of.

I have several If error vlookups on a spreadsheet that are looking up part numbers that contain letters and numbers (ex. ACM10256) from 3 separate sheets.

Example:

=IF(A8="","",IFERROR(VLOOKUP($A8,'07'!C:E,3,0),IFERROR(VLOOKUP(Master!$A8,'08'!C:E,3,0),IFERROR(VLOOKUP(Master!$A8,'09'!C:E,3,0),"Not On Contracts"))))

When I paste a part number in from one of the spreadsheets it's referencing, the vlookups work correctly. However, if I hard key verbatim, the part number, or if I paste from another source outside of the excel document, it returns #N/A. I have already made sure there are no trailing spaces, that the part numbers match, and that all columns involved are formatted to GENERAL (used text to column for each). Anyone have any ideas?

 

[Special-K99]

Make sure that the type of data is the same e.g. 123 is not the same as "123"
Test values by using ISNUMBER() or ISTEXT().

If VLOOKUP is producing an #N/A then whatever you're looking up clearly isn't there, no matter what the data looks like.

 

[Special-K99]

Just looking at your formula...
Should this

=IF(A8="","",IFERROR(VLOOKUP($A8,'07'!C:E,3,0),IFERROR(VLOOKUP(Master!$A8,'08'!C:E,3,0),IFERROR(VLOOKUP(Master!$A8,'09'!C:E,3,0),"Not On Contracts"))))

be this?

=IF(A8="","",IFERROR(VLOOKUP(Master!$A8,'07'!C:E,3,0),IFERROR(VLOOKUP(Master!$A8,'08'!C:E,3,0),IFERROR(VLOOKUP(Master!$A8,'09'!C:E,3,0),"Not On Contracts"))))

 

[MDlax30]

Thanks Special-K99, I went back and included Master! into the formula, however it did nothing to solve the problem. I also went back and checked the formatting using isnumber/istext. All the data is set to Text. So i'm still stuck.

It has to be something odd with formatting. If I go into the table array data in the vlookup and hard key the exact same part number, verbatim over top of what I pasted in from the original data set, it works. However, the pasted data does not. I even went back and in my table array, pasted a product code, then below it on the next row of my table array, hard keyed and it will pick up the pasted product number but not the part. I F2 and go into the actual cell and look to see if there is anything different, but the text bar up top shows the exact same for both. Completely at a loss

 

[Special-K99]

Formatting a cell does not change the data, it just changes how we see the data.

Ok, you've got two pieces of data, one where a VLOOKUp works and one where it doesn't.
Paste them both into two blank cells, lets say A1 and B1
In another blank cell enter

=(A1=B1)

it should say FALSE
You can then examine the cells closer to see what the difference is.

Alternatively

Attach 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.

 

[MDlax30]

Solved: I ended up finding out from a colleague that the database we used for the data pull has an unexplainable glitch. When the data is exported out of the system and to excel, it becomes unrecognizable to vlookups. So if I exported a cell that read "TEST1", I could vlookup this cell only to another cell that was copied from the imported "TEST1" cell. If I went in and vlookuped against a cell that with hard keyed "TEST1", it will not work. It almost makes exports from the system pointless, unless it is just to review. Thanks for the help though!

 

[Aladin Akyurek]

Does the following work for you?

Control+shift+enter, not just enter:

=VLOOKUP("*"&A8&"*",INDIRECT("'"&LOOKUP(REPT("z",255),IF(COUNTIFS(INDIRECT("'"&SheetList&"'!C:C"),"*"&A8&"*"),SheetList))&"'!C:E"),3,0)

where SheetList stands for a 3-cell range housing the names of the relevant sheets, that is, '07, '08, and '09.