Colunm Attachment

New Contributor


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)


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