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)?

The concatenated key you have (rptname_participantid_submitted) obviously presents a challenge. Is there any reason they can't be split up into three distinct fields?

Otherwise, is the format universal where the participantid is always separated by underscores? If so, you can make a calculated field to suss that out using a MID function.

Do participants only have one report each? If so, you can put the above MID function in column A of sheet B and then use a VLOOKUP to find the report name.

Dear gebobs

Thank you for your reply. To answer your first question, this is how the participants enter it. I don't know whether you are a betting man / woman but this is a university system so the odds of getting anything changed are zero.

As for the rest of your reply, yes this is how they enter it and they only have one report each. Therefore, I will give this combination of an MID function and VLOOKUP definitely a go.

I am neither a betting man nor a woman. ;-) I wouldn't touch that one with a ten foot pole.

If you want help setting this up, let me know. I can whip it up for you in a jiff.