Max between dates + hlookup?

[JM45T3R5]

Hello

I am trying to do a formula, but getting a little stuck.

Goal: find the maximum value between two dates, in a column with matched header.

I have created an easy theoretical example (attached)

The idea would be to find Jack's maximum score, between the two dates. As you can see in cell E24, I have actually managed to achieve the right result.

The problem I have is that it is done by manually specifying the row ("I:I"). This means that for all other candidates, I would have to manually enter the column each time. What I really want is some sort of function in the formula that matches the names in Column D to the names in Row 13, so that it knows which column to do the MAX formula for.

Hope that's clear and thanks in advance for any help!


Example.JPG