Find first entry, then second, then next etc from data

[Ally72]

Hi

I hope I can explain this properly. I did a search but it's hard to exactly how to search!

Currently using: Windows 10, Excel 2016

I have a s/sheet looking at a five year replacement plan for their PCs.
I have a list of Locations (Labs) in a uni that contains the number of machines, how much it will cost to replace and a year that (1-5).

On the right I have the years. Apart from Year 1, which is the one I'm experimenting with, they were populated manually. But if they decide they want to change, for example Lab B from the first year to the 3rd, it then needs to be done manually. What I'd like to be able to do, is, if the year is changed in column E, then the boxes / fields to the right get updated. I know I can do this with multiple pivot tables, but know that it could also be done with formulas.

In G5 for Year 1, I have used the Index and Match functions to return Lab B. I'm guessing I might need OFFSET, (which I don't know much about as yet) but before I went ahead and researched it all, thought I would ask.

Current formula (in G5) here is:
=INDEX($B:$B,MATCH(1,$E:$E,0))

I'm currently having trouble adding a screenshot - am working on it!

I'd be grateful for any help at all.

Many thanks

Ally

 

[Ally72]

He means me

WBD

You can go with Aladin's solution. You could also use the following in A17 and copy across and down:

{=IFERROR(INDEX(A$2:A$14,SMALL(IF($D$2:$D$14=$B$16,ROW($D$2:$D$14)-ROW($D$1)),ROWS($A$1:$A1))),"")}

WBD

Lol :D

Thanks so much everyone. It's working amazingly!

I just need to brush up on my =ROWS and =SMALL functions to understand it all!