Yeah this works for my carrier field as the value returned is value specified by me, because I know that it will say "UPS" if the data is found in UPS sheet.
What if to get my value I need my INDEX and MATCH, but I don't know how to put it together.
So it would do INDEX + MATCH but only if the reference is found (exact or part of it) and it tells me it is duplicate if I input it twice.

Since I could not put your piece of script together with my INDEX + MATCH, I've tried to come up with something on my own.
This gets my values, having 2 sheets "UPS" and "POLARSPEED".
Formula: copy to clipboard 
=IF(ISBLANK(main!$A2),"",IF(LEN(main!$A2)=7,IF(COUNTIF(A$2:A2,A2)>1,"Duplicate",IFERROR(INDEX(UPS!$B:B,MATCH("*"&LEFT(main!$A2,7)&"*",UPS!$A:A,0)),IFERROR(INDEX(POLARSPEED!$B:B,MATCH("*"&LEFT(main!$A2,7)&"*",POLARSPEED!$A:A,0)),""))),""))

It finds the part of value and tells me about duplicated just fine BUT one problem.
No value returned if the match is exact.

Sometimes people forget to include their initials in the reference number and the match would be exact.
So how to make it find the match exact OR partial? Tried to mess around with OR funtion but failed as well.
Or how to put together INDEX and MATCH with SUMPRODUCT and LEFT and make it work ??

Sorry but I am begginner please be patient with me :p