Reverse vlookup based on an account mask?

Hello world!

Please forgive any incorrect jargon ahead of time. But here is a gist of what I'm trying to accomplish:

A business has thousands of account code strings in their accounting software (i.e. 610.100.4000.6450.500). Our accounting software allows transactions to route to different people based on an account mask (i.e. ???.???.4???.????.??? where anything in the third element starting with a '4' would route to a person assigned that mask).

Given the volume of masks and potential for overlaps/misses, I was hoping to have a table showing column 1 - employee and column 2 - account masks assigned.

Then I wanted to take a list of all possible account codes and do some sort of vlookup (or other function), to tell me if that code has been assigned to someone. Any insight in advance would be greatly appreciated.

Thanks,
Aaron

Not sure if I follow entirely but...

Formula:

Please Login or Register  to view this content.

the above would return Buyer 1, Buyer 3, Buyer 3, Buyer 2 and No Buyer for Accounts listed in rows 11:16
if you want to return all instances of Buyer matches you can (the above will list the last found) but, you would need to outline how exactly you want them returned (separate columns, delimited string etc) -- and confirm XL version (2010?)

This looks great, thank you! I would like to be able to see all instances of returns, not just the last match found, and preferably in separate columns (one column for each buyer so I could get a count of how many buyers are assigned to an account code too.

I have the most recent 365 version of excel on my desktop, but not sure if I have the XL version.

If you have O365 then, using your sample file as uploaded

Formula:

Please Login or Register  to view this content.

the above should generate all buyers in the matrix C12:x16 -- where x will be determined by the last instance
so in row 13 you will get Buyer 1, 2 & 3 in C13:E13, respectively, and in row 14 Buyer 1 & 3 in C14:D14