VLOOKUP or MATCH not working ?why?

[RachelN76]

Hi everyone,

I use VLOOKUP and INDEX MATCH a lot, but today I'm stumped.
It's not working and I can't figure out why.

I have two tabs of data. One I have painstakingly copied and pasted from a pdf because the export function in adobe just wasn't playing . I'm on a deadline, so that seemed like the fastest solution at the time.
Second tab has been exported from a query builder attached to a database. I don't have access to the proper back end of the database, just this builder where I can drag and drop things, and specify certain criteria.

In both tabs I'm looking for a ten digit number that is formatted as text. I want to look it up from tab A, and then return third column along from tab B. Simple.
But it won't work! If I select a cell in A that I KNOW is in B, and do Ctrl+F - it can find it no problem.
But it doesn't seem able to see it with MATCH or VLOOKUP.

Any ideas why this might be? Unfortunately I can't share the data for you to look at it. I can only think it's something to do with the way they are formatted. I've tried cutting and pasting values. I've tried turning them into numbers (tab A is happy with that, tab B is not). I was struggling enough to think of a way to link these two things in the first place, but if this doesn't work, then I think I'm really stuck, and can't think of a better way than trawling through 700 entries in two different databases!

 

[rlv01]

Usually it means that there are leading or trailing spaces, or non-displaying characters (either in the lookup value or the lookup range) that are preventing a match.

 

[RachelN76]

Thank you - I looked at that, and couldn't visually see anything. Cell=Cell resulted in TRUE so I don't think it was that.

I reformatted both columns using LEFT to get the text out, and then cut and pasted values from both of those, and then it worked using XLOOKUP. I still don't know why, but at least it worked!!

 

[awoohaw]

Rachel, this may take a few minutes. But in the "pdf copied sheet" I'd format all the problem (or all) columns to "General". then use the TextToColumns data tool for each column.