Index & Match Formula +1 Increments

[mbhogal]

Hi All,

Formula: =INDEX(B:B,MATCH("Site B",A:A,0)+0,1)

Data:

Sites Full Name
Site A Mr A
Site A Mr B
Site B Mr C
Site B Mr D
Site B Mr E
Site B Mr F

When I drag the following formula down, I would like the increments to change by 1 so for eg. the formula for the cell below would be =INDEX(B:B,MATCH("Site B",A:A,0)+1,1).

The results should be as follows:

Mr C
Mr D
Mr D
Mr E
Mr F

Can you provide a quick solution please? Many thanks in advance.

[Pete_UK]

You can do it like this:

=INDEX(B:B,MATCH("Site B",A:A,0)+ROWS($1:1)-1,1)

Hope this helps.

Pete

[mbhogal]

Thank you, but this works to an extent however I only want Site B data. So the data is as per below:

Sites FullName
Site A Mr A
Site A Mr B
Site B Mr C
Site B Mr D
Site B Mr E
Site B Mr F
Site C Mr AA

And the formula provided the following results:

Mr C
Mr D
Mr E
Mr F
Mr AA

Can you help please?