Consecutive cells match a defined sequence then vlookup

Hi!

I am looking for a code, which should compare in column A if there is a specific defined sequence, which is given in the column E (two sequences). When this sequence is found, then the code should lookup the value in column B from the first and last letter of the sequence and write these values in the format "Value 1 - Value 2" in column H, when it is the data for George, otherwise in column I, when it is the data for Peter.

There are several sequences in the data for George and Peter. Therefore the code should write a first entry and then also the second entry in the next row. I hope I explained it reasonable. The sheet is attached. Thanks a lot guys!

Hi IndigoSK,
try this

Please Login or Register  to view this content.

Thanks nilem. Thatīs ist. Very nice!

I am struggling a litte bit with understanding the code. When I am adding a new sequence under the data of George or Peter, what do I have to change, that there is a new, a third line in the results column. Thanks!

Thanks nilem. Thatīs it. Very nice!

I am struggling a litte bit with understanding the code. When I am adding a new sequence under the data of George or Peter, what do I have to change, that there is a new, a third line in the results column. Thanks!

you need to create a named range, such as "John"
In this case, the code looks like

Please Login or Register  to view this content.

and override the resulting array

Please Login or Register  to view this content.

Of course, other approaches and solutions are possible.

Hi IndigoSK,
try this function which has Sequence as what type of sequence you want and range as

Please Login or Register  to view this content.

Hi fshbet,

I didnīt get your code to work. Donīt know whatīs the problem. But thanks a lot. The code from nilem works fine. Thank you your explanation too.