comparing 2 cells + vlookup working only on the first result + decomposing a cell string

Hello all,

I am trying to do something that's "in theory" fairly simple :

compare a database that comes from one software with another from another software.

Numbers are formated very diffrently, but I managed to trim and clean them.
Then I end up with numbers like this:
AM654HSA
MA87878F
MA01010SA

MA01010SA from one database could be MA01010PQ from the other.

In order to find these differences I decomposed these numbers in 3 cells to be able to compare the 3 parts of the numbers separetly:

this gets me MA in column 1, 01010 in column 2 and SA in column 3.

So far so good. But when I do a vlookup, I find 2 problems.
1- I sometimes get a N/A error, that I think is simple some formatting issue with the cells. No biggie.
2- Vlookup seems to always report the first number is finds that matches the querry. The problem is that I could have several number that are the same in column 2, but not in it's original form.
ie:
AM1111SA
AM1111SW
MA1111SA
but excel will always report only the first 111 that it finds in column 2, and then this whole thing does not work for me!
Please help me out here.

My file is attached.

Maybe there is a much easier way of doing this as well?

Thanks for any input.

Gerry

When you say "compare a database", what are you actually looking for Excel to give you as an output? I can see that your test file doesn't work how you want it to, but I am unclear on your end goal to help you get there.

Hi there,

I want the excel to tell me if the AM/MA, the #### and THE XXX (3 last digits) from one page, match the ones on the other page.
That's why I decomposed it in 3 columns.

So if a number is right but the AM/MA code or the 3 last digits are wrong, I want it to tell me were they are.

AM1495S from one page should match the AM1495S from the other. If there was an error entering the data, and the number turned out to be AM1495LL instead, it should point to the LL field (will be conditional formating in red, say)

Thanks

So basically, if it can't find an exact match, tell you there's an error?

Can you do a VLOOKUP on the entire string, rather than separated into three pieces?