VLOOKUP - Lookup value slightly different to values in lookup table

[Ben H]

Hi All!

I am currently having trouble using a VLOOKUP function. The reason I have a problem is my Lookup values are slightly different to how they appear in the lookup table, but they are similar. They are in fact serial numbers, but the two data export files I am using (annoyingly!) display the codes in slightly different orders. For example, I want to look up a price associate with the code: P 1 AD-N1414May99, but it appears as N1414May99/P 1 AD in the table. They are written in different orders with slashes versus hyphens etc, but I want my function to treat these codes as exact matches. Is there any ideas as to what I can do to achieve this, bearing in mind that I have hundreds of codes which in many cases only differ by one character. Please see below table as an example.

Lookup Values
P 1 AD-N1414May99
P 2 AD-N1414May99
P 3 AD-N1414May99
P 1 AD-N1256May01
P 2 AD-N1256May01


Lookup table
N1414May99/P 1 AD -----$100
N1414May99/P 2 AD -----$120
N1414May99/P 3 AD -----$110
N1256May01/P 1 AE -----$115
N1256May01/P 2 AE -----$110


It's important to realise that I want the codes to be treated as exact matches which is difficult given how similar the codes are to each other. I then want the function to take the price from the lookup table and insert it into the column beside the values.

Thanks a lot for any help!

 

[jmthompson]

Is is always true that your lookup table serial numbers = the 10 right characters of your lookup values, "/", the left 6 characters of your lookup values?

 

[Ben H]

Thanks.

Unfortunately, no.

However, each code has two parts, and when I produce my lookup table, the 2 parts are swapped, and hyphens appear instead of slashes. So is there maybe a way I could automatically edit the codes to re-write them in the same format. There are hundreds of them with varying amounts of characters (but always two distinct parts)