Match and Add info from separate workbooks with different formatting. xlookup not working

Hey All,

I have 2 workbooks where I have a primary key identified and I want to bring a specific set of information over from the other workbook with the least amount of manual manipulation possible. In simple terms the Primary Key is listed as 'Set' in one workbook and 'Name' in the other. I need the 'code' to be matched and brought into the other workbook.
The sample workbook attached is a dumbed down version but I did include most of the variation seen in the 2 workbooks (blanks, duplicate codes, duplicate names, not the exact same number of entries, not every code has a match and not every name/set has a code, etc.).

I realize that with a bunch of manual manipulation this is not a difficult task, the key here is to ideally bring the workbooks together into 2 separate tabs, insert function, define the arrays, and voila. The further the solution gets away from this level of simplicity the less likely it becomes feasible to do.

Sheet 1 - Blanks, duplicate names are intentional, not in order of any sort is intentional. I need the code from Sheet 2 in sheet 1. The real sheet has about 6000 rows and 20 columns
Sheet 2 - Same as sheet 1, the blanks, order, names, etc. are all intentional. The real sheet has about 25,000 rows. A easy step would be to remove the 'generic form' duplicate names with different codes if this causes havoc with a function.

I thought xlookup would do the trick but havent been able to get it to work(Transparency: I haven't used xlookup before). Just get the attached error. I havent tried embedding either.

Thanks for the help!