Reference or Vlookup against merged cells

Hi
I'm trying to come up with a solution with bring across a promotional comment from a merged cell.
On a second worksheet i'm trying to reference in the marketing comments but the joyous excel Merged cell issue means it will only bring in the comments from the first product with the merged cell next to it.
What it needs to do is bring across the same comment in this case rows 1-6, 7-11, 12-13, 15-16.
These references will change with each activity so i cant set up a static formula to do it.
The full spreadsheet as 900 lines so needs to be a quick fix than unmerging and filling down.

thanks
Adi

One way

Formula:

Please Login or Register  to view this content.

The word "code" in the formula should be replaced with a reference to the code you want to look up in the table.

thanks, it works a treat,
can i just ask how the REPT function works, and what reference the "z" has?
i understand that the second reference is number of repeats but not the first
Adi

It returns a string of z's, 255 charachters long, which would fall after any of the strings in your comment column if it was sorted alphabetically.

Using that as your lookup reference value with an approximate match will return the last comment in the range defined by the second part of the formula.

thanks, makes sense now, glad to know it can be used in other description matches.
thanks for your help, much appreciated.