Nested LOOKUP formula with TRIM and FIND

[janema]

I have been trying to create a nested xlookup formula with a trim and find, but wonder if this is possible. The two formulas I am trying to combine are as follows:

=XLOOKUP(F4,'EmpExport(3.11.24)'!E:E,'EmpExport(3.11.24)'!BC:BC)
&
=TRIM(RIGHT(A2,LEN(A2)-FIND(",",A2))&" "&LEFT(A2,FIND(",",A2)-1))
The source data of that lookup formula returns the name of an employee in "Last Name, First Name" format, but I am trying to add in the second formula to format that source data in "First Name, Last Name" instead.

Please help! Thanks so much in advance.

 

[DanteAmor]

To MSO365:

=TRIM(LET(a,XLOOKUP(F4,'EmpExport(3.11.24)'!E:E,'EmpExport(3.11.24)'!BC:BC),TEXTAFTER(a,",")&", "&TEXTBEFORE(a,",")))

 

[janema]

To MSO365:

=TRIM(LET(a,XLOOKUP(F4,'EmpExport(3.11.24)'!E:E,'EmpExport(3.11.24)'!BC:BC),TEXTAFTER(a,",")&", "&TEXTBEFORE(a,",")))

THANK YOU SO MUCH! It was driving me crazy. I tried it a dozen ways, but missed the "a," after the LET!