SOLVED

Colunm Attachment

%3CLINGO-SUB%20id%3D%22lingo-sub-2209137%22%20slang%3D%22en-US%22%3EColunm%20Attachment%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2209137%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20want%20to%20reference%20to%20first%20sheet%20for%20names%2C%20but%20without%20dots%2C%20and%20then%20reach%20the%20related%20column%20values.%20I%20was%20able%20to%20write%20this%20function%20at%20the%20second%20sheet%20but%20couldn't%20finish%20it%3A%20%22%3DIF(LEFT(Sheet1!B2%3B%20FIND(%22.%22%3B%20Sheet1!B2)%20-1)%26amp%3B%20(%22%20%22)%20%26amp%3B%20RIGHT(Sheet1!B2%3B%20LEN(Sheet1!B2)-FIND(%22.%22%3B%20Sheet1!B2))%3DB2%3B%20Sheet1!C2)%22%3C%2FP%3E%3CP%3ESince%20I%20can't%20change%20the%20order%20input%20in%20this%20function%2C%20I%20need%20help%20with%20the%20attachment%20part%20to%20write%20a%20different%20name%20to%20get%20a%20positive%20result.%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2209137%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

Hello,

I want to reference to first sheet for names, but without dots, and then reach the related column values. I was able to write this function at the second sheet but couldn't finish it: "=IF(LEFT(Sheet1!B2; FIND("."; Sheet1!B2) -1)& (" ") & RIGHT(Sheet1!B2; LEN(Sheet1!B2)-FIND("."; Sheet1!B2))=B2; Sheet1!C2)"

Since I can't change the order input in this function, I need help with the attachment part to write a different name to get a positive result.

Thank you for your help in advance.

3 Replies
best response confirmed by oremert (New Contributor)
Solution

@oremert 

That could be

=XLOOKUP(SUBSTITUTE(TRIM(B2)," ","."),Sheet1!B:B,Sheet1!C:C,"not found")
Thank you for the quick reply!

@oremert , you are welcome