Index/Match question

[Peterw_2506]

Hi everybody. Can somebody give me a little help please with a lookup formula.

Here is the problem;

I have lookup cells ($H$8:$BE$8) they represent months e.g. 1, 2, 3, 4, etc. etc. 50 of them

I have cells that contain data ($H$11:$BE$611)

Now whatever month I what to reference, I want to retrieve the last cell that contains a numeral in the respective column.

I have been trying to use =INDEX($H$11:$BE$611,MATCH(9.99E+307,range),MATCH(2,$H$8:$BE$8,0)) range = $11:$611 of the respective column in $H$8:$BE$8 (50 columns)

However, because this is referencing multiple columns I just cannot close to my desired result. If I manually allocate a column – yes that works, but that is not the desired result, it needs to automate.

If somebody can guide me, I would be grateful.

Thank you in advance.

 

[jasonb75]

For just the last value in the column, try

=LOOKUP(9.99e+307,INDEX($H$11:$BE$611,0,MATCH(2,$H$8:$BE$8,0)))

 

[Peterw_2506]

Thank you Jason. This works beautifully. Sometimes a persons can't see the forest due to the woods.