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.

=IF(ISERROR(MATCH($J2,'Duplicate customers'!$A:$A,0)),VLOOKUP($J2,'Customer mapping'!$A:$D,4,FALSE),IF(COUNTIF(OFFSET('Duplicate customers'!$A$1,MATCH($J2,'Duplicate customers'!$A:$A,0)-1,1,COUNTIF('Duplicate customers'!$A:$A,'Data to map'!$J2),1),'Data to map'!$H2)>1,VLOOKUP($B2,OFFSET('Duplicate customers'!$A$1,MATCH($J2,'Duplicate customers'!$A:$A,0)-1+MATCH($H2,OFFSET('Duplicate customers'!$A$1,MATCH($J2,'Duplicate customers'!$A:$A,0)-1,1,COUNTIF('Duplicate customers'!$A:$A,'Data to map'!$J2),1),0)-1,2,COUNTIF(OFFSET('Duplicate customers'!$A$1,MATCH($J2,'Duplicate customers'!$A:$A,0)-1,1,COUNTIF('Duplicate customers'!$A:$A,'Data to map'!$J2),1),'Data to map'!$H2),2),2,FALSE),VLOOKUP($H2,OFFSET('Duplicate customers'!$A$1,MATCH($J2,'Duplicate customers'!$A:$A,0)-1,1,COUNTIF('Duplicate customers'!$A:$A,'Data to map'!$J2),3),3,FALSE)))

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:

=IF(A3="",E2,A3)

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