How to set cell value dependent on another cell with variable position

Question

I got a strange task to perform and I can't imagine how to do it or even come up with a term to google, since I'm not that good with excel formulas, and I am not allowed to use VBA:

If column 'D' contains either an 'S' or an 'H' the following needs to be done:

1. Go to column 'E' in the same row
2. Find the first cell in column 'E' that holds a value by moving upwards
3. Go to the right to column 'F'
4. Copy that value to the cell in column 'B', in the row where you started

I know it's confusing, so I'm adding a screenshot:

There are some conditions:

• As I mentioned, I am not allowed to use VBA.
• The amount of rows can change, I guess more will be added.
• It apparently needs to be only a formula so it can be copy and pasted when new rows are added.

I hope I made myself clear, since english is not my native language. Feel free to ask questions if something is unclear.

Answer 1

Try the following:

Formula in B2:

=IF(OR(D2="S",D2="H"),INDEX(F:F,MATCH(LOOKUP(2,1/(ISNUMBER(E$1:INDEX(E:E,ROW(E1)))),E$1:INDEX(E:E,ROW(E1))),E:E,0)),"")

Drag down...