As luck would have it, whilst trawling through several other forums for the answer to this, someone asked almost exactly the same question.

So I took that formula and adapted it slightly to fit your needs.

With this version you don't even need the column B that extracts just the last six digits, it merely searches the last six digits of each entry in column A directly.

Hopefully you can adapt it to meet the requirements of your specific workbook, although I'm happy to assist you with that too if need be.

Please don't ask me to explain exactly how it works as I'm still trying to fathom that myself :)