Index Match & VLookup - Never had this happen before (partially working??)

Trying to look up information on a second sheet. It's working for the majority of the content, but I am getting N/A for some items that actually are there. I highlighted a few examples in red. They come up right away if you search for them in sheet two.

I can't add an attachment so I hosted it with a direct download link: https://s3-us-west-2.amazonaws.com/2...+of+Book2.xlsx

FIRST PERSON TO SOLVE IT WINS! To minimize some obvious responses, I am:

• The search criteria/data exists
• Using exact match
• Locked the table reference
• No column has been inserted
• The Table has not gotten bigger
• VLOOKUP is not looking left
• Table contains no duplicates
• No funky formatting.
• Freshly pasted into a new workbook (paste values)
• etc...

Please Login or Register  to view this content.

Please Login or Register  to view this content.

first problem

dont use whole column reference as lookup_value

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

the ones coming as #n/a are the differences between Text and number format
brief explanation can be found here

https://exceljet.net/formula/vlookup...mbers-and-text

3rd problem
it seems you want partial matches as well

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

this "could" work

you may get the wrong result sometimes using this method

more reading for vlookup found here
http://www.excelfunctions.net/Excel-...-Tutorial.html

Originally Posted by S Thibault

I want exact matches, because.. They are exact matches. Have you actually managed to get rid of the errors, because the number formatting does not seem to make a difference. Thank you for your help. Like why is DN1002
not matching?

did you click on the hyperlink i put?
it goes to sheet2!B393
see what is showing in that cell

Originally Posted by S Thibault

For example: SIPT40PN is matching up with the wrong title in your example. I need to do this with about 20,000 rows and they need to match perfectly.

if you require exact then change it back to formula in post #2

what happens is item like
IP8364-C

on sheet 2 it shows as FBA-ip8364-c (assuming this is the one you want to match)
as it is not exact it will give #n/a error

**
attached file with formula in post #2
highlighting hyperlink to why your getting a #name error

Originally Posted by humdingaling

if you require exact then change it back to formula in post #2

what happens is item like
IP8364-C

on sheet 2 it shows as FBA-ip8364-c (assuming this is the one you want to match)
as it is not exact it will give #n/a error

I dont want it to match that. That is the wrong SKU (there is no match for that one). Thats the reason I highlighted some examples in red. Those are the ones I am having difficulty with. Are you beginning to see why this is so bizzare?

Originally Posted by S Thibault

I dont want it to match that. That is the wrong SKU (there is no match for that one). Thats the reason I highlighted some examples in red. Those are the ones I am having difficulty with.

if it is not showing on your list...you will get an #n/a
isnt that what you want to see? if not what do you want to see?

Search for 504575 on sheet 1. Then search for it on sheet 2. How are those not exact matches?

I meant 504575. Sorry Im getting mixed up between your version and my version sheet.

Originally Posted by S Thibault

I meant 504575. Sorry Im getting mixed up between your version and my version sheet.

please go to the website i posted in Post #2
it explains what the issue is

i did not use the method of how to get around it though

i made both columns "TEXT" fields using text to column

not a problem
glad to assist

learning yourself is so much better than just getting the answer

in your particular example it wasn't the issue causing the error
this was my error, i wasn't aware can vlookup/match& index this way

though i would find it rather different to how i would code something like sumproduct
so for consistency for myself i prefer specific vlookup values

unless your experiencing performance issues
its probably fine to continue using column lookup values