Copy data on Condition

[Jonathan78]

Hi everybody,

This one is hard to describe so I hope the attachment will make it more clear.

I'd like to get the cell in column E, which has the word "dept." on the same row in column B, copied down in column D, till the word "name" occurs in column B.
One extra difficulty is that I'd like it to start from one row up.

I hope the enclosed example is more clear.
How the result should look like is shown in range K:N

[NBVC]

Perhaps with a formula:

=IF(B7="name",LOOKUP(2,1/(B$8:B8="dept."),E$8:E8),D6)

copied down.

[StephenR]

This seems to work:

Please Login or Register  to view this content.

[Jonathan78]

Thanks NBVC and Stephen, I used NBVC's formula, it worls great!