Okay, first, adjusting it for your workbook.......
=INDEX('Sheet 1'!$B$2:$B$1000, MATCH(A2, 'Sheet 1'!$A$2:$A$1000, 0))
Since you combined first and last names, you don't need to use CNTRL SHFT ENTER and it's simplified. Also, Sheet1 (and 2) became 'Sheet 1' because your workbook has spaces in there. This still end up giving #NA's because on Sheet 1, you have a double space between the last and first name. Just select column A, CNTRL H to bring up the Search and Replace and "Find What" type in double space, "Replace with" type in single space and replace all. Does that work for you now?