Index match gives wrong information or does not find item

When using index match function either it gives me wrong information or does not return information when I verified there is.

Is there another function that is more reliable? Or am I doing it wrong all this time. I just like not to worry I do not give the wrong data. First formula seems to show all data but when checking it is mostly wrong.

Current function being used.

=INDEX('Sharpoint data'!L2:L869,MATCH(C2,Dragon!C2:C523,0))

Or (which works much better may be 2% inaccurate

=INDEX('Sharpoint data'!L4:L870,MATCH(TEXT(C4,"#####"),'SharePoint data'!I4:I870,0))

Data need to be in column highlighted in yellow if you want to see. (see attachment

Thank you

The index and match ranges need to be the same size.

Incorrect solution - sorry, please ignore.

Check your data: many names have leading or trailing blanks so MATCH will fail.