How Do I Find and Return a String When Only Part of that String is Known?

I'm sure this should be easy but I am stumped. I have used pretty simple Vlookup and Index queries in the past but am struggling to get my head round this: I am using Excel 2007.

I have a column of data A1 to A5000. It contains product ID's in the following format:

1ABC12AB-1234

I want to find a record using just the 1234 part (which is unique to each record) of the string but return the complete cell contents to populate another column.

The first part of the string is not always the same length, it could be 1ABC12ABCD etc but the part after the hyphen is always 4 characters long. Also there are no duplicate records.

Basically i want to type: "1234" in one cell and get "1ABC12AB-1234" to magically appear in the cell next to it.

Any help would be much appreciated!

try using match function

Use this as your starting point:
=OFFSET(A$1,MATCH("*" &C2 &"*",A$2:A$6,0),0)