Compare columns between two sheets, if match, copy value of another column

Anyone have a solution for the following:

I have one sheet with a list of names and another sheet with the RSVP response. The task is to compare the names and companies in both sheets, if there is a match, copy the value of the RSVP Status column.

The order of rows in the names sheet is different from the RSVP sheet. Names in the first sheet may or may not have a corresponding name in the second sheet and vice versa.

Name is stored as First Name and Last Name. We have to concatenate the names to compare because of some rows where the entire name is stored in the First Name column or the Last Name column.

Comparing names:
- Concatenation -- First name first, and then last name first (e.g. John Smith and Smith John) (2x)
- Do this for both sheets and compare each permutation (2x)
- Total 4 permutations (2x2)
- Remove whitespace, ignore case

Also, check if the company name matches to ensure that it is the same person

If there is a match in the second sheet, copy the RSVP status in the matching row to the first sheet. Else if no match found, display X

Account for variations in spelling of column names e.g. "First Name" or "FirstName", trim whitespace of columns, ignore case. Location and order of columns may vary. E.g. sometimes First Name is in Column B, sometimes in column E etc. First Name is not necessarily next to Last Name, RSVP Status is not necessarily in last column

The solution should not have intermediate cell outputs, all information should be within a formula within the cell in their respective rows in the first sheet.

Please see attached for sample file. Thanks