I have a spreadsheet in which I have the transaction details of a number of swap trades. These trades have a reset of their interest every three months. This means that every trade can have a different number of columns in my spreadsheet since every reset rate is stored. (So for every record has it's own header row!) . There is also a field called Resetno which tells you how many times there has been a rate reset. This number corresponds with the number of times one will see the column name ResetRates inthe file.

What I now need to be able to do is to return the last resetrate to a certain cell in my spreadsheet. THis however turns out to be very hard. Hlookup doesn't work because there are more than one column named Resetrates (I do always need the last one though!). Vlookup on ResetNo doesn't work because the value I want to have returned is in the row below it.

Does anyone have an idea on how to do this??

I have attached an exmple of what my data looks like.

This should do it:

=INDEX(2:2,MATCH(9.99999999999999E+307,2:2))

If you want to automate this, then in cell B10, you can put this formula and copy down:

=INDEX(INDIRECT(MATCH(RIGHT(A10,1),$A$1:$A$6,FALSE )&":"&MATCH(RIGHT(A10,1),$A$1:$A$6,FALSE)),MATCH(9 .99999999999999E+307,INDIRECT(MATCH(RIGHT(A10,1),$ A$1:$A$6,FALSE)&":"&MATCH(RIGHT(A10,1),$A$1:$A$6,F ALSE))))