Vlookup/If formula assistance

Hi everyone,

I am in the process of mapping customer changes in our database. Please see attached excel spreadsheet:

* tab 1 shows 4 columns: the old customer ID, the currency the customer is billed in, the legal entity the customer belongs to and the new customer ID.
* tab 2 shows the same information as the first; however this tab highlights how one old customer ID can equal multiple new customer IDs since the new IDs are further segregated by billed currency and legal entity.
* tab 3 shows the raw data I am trying to map, the old customer ID is in column J and currently there is a vlookup formula linked to tab 1 in column K.

My question is this: is there a formula I can write that can somehow "check" the customers that may be on tab 2? Ideally what I'd like to do is the following: is the old customer ID on tab 2? If no, pull new customer ID from tab 1. If yes, then check column C on tab 3 against column B on tab 2 and select correct customer based on currency [after first checking for customer ID], if currency is the same then check column B on tab 3 against column C on tab 2 [after first checking for customer ID and currency].

I know I need to correct my raw data to change column B to company name vs company code since that is how the other tabs are formatted.

I believe I can use some kind of nested "if" formula, but I'm striking out. Any help you can provide would be appreciated!

tough to be 100% sure since your company ID doesn't actually match..... but I think this does it.

Please Login or Register  to view this content.

forgot to mention you need to duplicate all the data labels on the Duplicate customers tab. i.e. don't leave any blanks in customer ID or Currency... Easy to do with a quick helper column:

Formula:

Please Login or Register  to view this content.

placed in E3, drag right and fill all the way down. then copy paste values in a3