Results 1 to 3 of 3

Thread: lookup

  1. #1
    Join Date
    Oct 2003
    Posts
    61

    Unanswered: lookup

    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.
    Attached Files Attached Files
    Nobody dies a virgin because life screws us all!

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    This should do it:

    =INDEX(2:2,MATCH(9.99999999999999E+307,2:2))
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    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))))
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •