Index formula giving error

[Emons]

I have simple data set with 2 columns in sheet 1. i am trying to get column B from Sheet 1 in to Sheet 2 by matching column A. but receiving error with index and match formula. can anyone have a look at this and provide solutions? excel sheet is in this link.

 

[rondeondo]

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.

 

[Fluff]

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

=INDEX(Sheet1!$B$2:$B$52959,MATCH(A1&"",Sheet1!$A$2:$A$52959,0))

 

[Emons]

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.

Thanks it worked.

 

[Emons]

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

=INDEX(Sheet1!$B$2:$B$52959,MATCH(A1&"",Sheet1!$A$2:$A$52959,0))

Great. it worked now. i am using Office 365 and updated in profile now. thanks

 

[Fluff]

Glad we could help & thanks for the feedback.
Also you do not need to use Ctrl Shift Enter for that formula. In fact as you are using 365 you never need to use CSE again.