Failed attempts to link two sheets

Dear all,
Happy new year and all the best for 2024!

I have a document with two excel sheets, both including in column "THE CRITERIA" a wide number of row with the same text. The information differs in the next columns, and I would like to replicate some information in both sheets. It seems that the problem I am facing is that the information in the column A is, in some cases, a large number of characters.

There below the failed attempts:
- VLOOKUP: the text column A is often >350 characters;
- Vlookup(right or left): the text in the cells can either (i) start the same way, (ii) end the same way, (iii) start and end the same way, but still have some different information in the middle. Sometimes it is just one word in the middle which makes the difference;
- INDEX MATCH: #VALUE error....
- Sorting columns on both sheets, and to link an ID to each cell with =IF(G2210<>G2209;SUM(H2209+1);H2209) -> the sorting didn't worked in the large cells and therefore did not work


Thanks a lot in advance!!!

Dear Glenn,

Thanks for your help.

I am trying to find a way to link both sheets: either with a common ID per "THE CRITERIA", either with a formula.
I have added the column "ANIMAL" in sheet "Ambition", and the aim would be to have the "ANIMAL" aslo in sheet "ANALYSIS" based on "THE CRITERIA".

Trying my best to be clear, I am truly sorry if is not

Thank you so much for your help!!!

Camille

Dear Glenn,
Many thanks!!
I have added one more "mid" criteria : and it works! You are the best! Have a wonderful day

=INDEX('Ambition'!$B$6:$B$229;MATCH(1;EXACT(LEFT(G6;255);LEFT('Ambition'!$B$6:$B$229;255))*EXACT(MID(G6;256;255);MID('Ambition'!$B$6:$B$229;256;255))*EXACT(MID(G6;512;766);MID('Ambition'!$B$6:$B$229;512;766))*EXACT(RIGHT(G6;255);RIGHT('Ambition'!$B$6:$B$229;255));0))