I have two data sets on two separate worksheets (in the same document). I now have to allocate a certain entry from worksheet B to an entry in worksheet A. A concrete example: In worksheet A I have a participant ID (e.g. 1234) and a corresponding participant name (e.g. Smith). In worksheet B I have report names with the participant ID as part of it (e.g. Water Contamination_1234_submitted) and the report name (e.g. Changes in Water Contamination in April).

My challenge is the following: What I would like to do is enter the report name from worksheet B in worksheet A behind the correct name, identified by the ID number. Without any automatisation I will have to sift through worksheet B entry by entry and then manually enter the required information in worksheet A.

The question, therefore, is (and I realise this is a long shot): does Excel have a function that does not only make it search the entire worksheet B for a certain element of data (in this case the ID, e.g. 1234) but can also identify this bit of info if it is part of an entry in a cell and not the only info in there (i.e. Water Contamination_1234_submitted instead of just 1234)?