I've made a little example spreadsheet, it is attached.

I want to input reference number and pull the data off other sheets, if that number is found there.
But it only finds the values if the reference number is exactly the same.
With every reference number there actually come initials, from the person dispatching the box/pallet.
So in UPS sheets they would look like: 5628052MZ, 5630001KD...

I do not know who dispatched the box so Ive only got the numbers.
In that case values are not returned.

What shall I do to overcome this ?

>>EDIT: Ah yes I forgot about something. I have a carrier field. What I need:
If any value is found in any sheet, return the name of the sheet where value has been found to the carrier field.
So if I typed in the reference number and formula would find a date for it in "UPS" sheet, the carrier cell for that row would now say "UPS".
Is it possible?


>>EDIT2:
I've come up for this with the carrier field so far:
Formula: copy to clipboard 
=IFERROR(IF(MATCH(main!$A3,ups!$A:A,0), "UPS", IF(MATCH(main!$A3,TFS!$A:A,0), "TFS", IF(MATCH(main!$A3,OT!$A:A,0), "OT", IF(MATCH(main!$A3,polarspeed!$A:A,0), "POLARSPEED")))),"")

But only returns value for UPS, doesnt go further. I know this is very crappy scripting of mine, so please help .


Many thanks,
Matt.