Help with Formula

[kittymongo]

Hello,

I have 2 spreadsheets I am comparing data on. One column is a concatenation of 2 cells and this is on each spreadsheet. I need to compare the data between the 2. Spreadsheet A has the concatenation with a data point that won't be a one to one match to spreadsheet B. How can I write a formula to do this comparison? I understand there may be an additional step that may need to be done. Below is an example of what I am trying to do.

If the data in the first column has the account name with code 123 at the end, then the data in the other column will have the account name with code 357 at the end. For what I need, that would be considered a match for what I am comparing. Is there a formula to use for this?

Spreadsheet A Concatenation	Spreadsheet A formula	Spreadsheet B Concatenation	Spreadsheet B formula
ABCDEFG123		ABCDEFG357
HIJKLMNO789		HIJKLMNO987
PQRSTUV102		PQRSTUV201

 

[felixstraube]

If the lenght of the text portion of your code is always 7 characters long then how about this?:

Libro3
	C	D	E	F	G
3	Spreadsheet A Concatenation	Spreadsheet A formula		Spreadsheet B Concatenation	Spreadsheet B formula
4	ABCDEFG123	ABCDEFG357		ABCDEFG357	ABCDEFG123
5	HIJKLMNO789	HIJKLMNO987		HIJKLMNO987	HIJKLMNO789
6	PQRSTUV102	PQRSTUV201		PQRSTUV201	PQRSTUV102
Hoja1

Cell Formulas
Range	Formula
D4:D6	D4	=XLOOKUP(LEFT(C4,7),LEFT($F$4:$F$6,7),$F$4:$F$6,"not found",0)
G4:G6	G4	=XLOOKUP(LEFT(F4,7),LEFT($C$4:$C$6,7),$C$4:$C$6,"not found",0)