=INDEX(Sheet1!$B$2:$B$52959,MATCH(A1&"",Sheet1!$A$2:$A$52959,0))
I'd guess yoou have the UNSPSC codes as text in Sheet 1 and numbers in Sheet 2. try adding an empty string in your match, ie. match(a1&"".... this will force it to be text when searching.
What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
How about
Excel Formula:=INDEX(Sheet1!$B$2:$B$52959,MATCH(A1&"",Sheet1!$A$2:$A$52959,0))