Performing a very complicated VLOOKUP

[akhtarn11]

Hi, been using this forum for a long time but have never had to post until now. I have an extremely difficult VLOOKUP I'm trying to perform.

I have two sheets (S1 and S2). I have to populate Column F in S1 with ACCOUNT OWNER using Column D which contains ACCOUNT. S2 contains a list of which ACCOUNT OWNER (Column A) owns which ACCOUNT (Column C).

Sounds pretty simple right? Well here is the wrinkle. The text for ACCOUNT in S1 (D) rarely results in a direct text match with ACCOUNT in S2 (C). For example, ACCOUNT in S1 will have "Pampers" while ACCOUNT in S2 will have "P&G - Pampers" or "Pampers Pull-Ups".

There are thousands of accounts with varying text field sizes and complexities. Is there a way we can tell Excel "Populate F in S1 with the Account Owner in S2 if the Account in S1 partially matches the Account in S2"?

I know this is extremely confusing but it's the only way I can think of explaining it...any help will be much appreciated.

[NBVC]

Have you tried perhaps using wildcards?

e.g.

=VLOOKUP("*"&A2&"*",S2!A:B,2,FALSE)

where A2 contains "Pampers" and S1 column A contains "P&G - Pampers" or similar.

Adjust the actual Vlookup to suit your tables and references.