How do I get Excel to return the full value based on the last few digits?

Question

I have the last four digits of a code, and I have a list the full ten digit codes. What I'm trying to do is enter the last four digits and use an Excel function to return the complete code from a list based on the last four digits.

For example:

Partial Code (Last four digits)

A2C2

Full Code (From a list of complete 10 digit codes)

470B0FA2C2

How do I get Excel to return the full code based on the partial code? Any help would be greatly appreciated.

Answer 1

you could create a 2nd column of just the 4 digit codes, then use a VLOOKUP formula to look up your partial code and return the full code

so let's say your 10 digit code is in Column A (starting at A1).  Select column A and insert a new column. This should shift A over to B and and now you have a blank A column.

in A1 enter

=RIGHT(B1, 4)

assuming B1 is 470B0FA2C2, you'll now have

A2C2 in A1

copy this all the way down as far as needed.

now let's say you enter your partial code A2C2 in C1

over in D1 enter

=VLOOKUP(C1, A1:B1000, 2, FALSE)

(where B1000 is the end of your list)

this will look up the value from C1 (A2C2) and try to find a match in column A and if it finds a match, returns the value from column 2 (B) of your range.

the "false" at the end just tells excel to find exact matches only