locked
How can I look at a group of IDs and pull different dates? RRS feed

  • Question

  • I’m trying to think of a way of looking at groups of IDs and pull different dates based on one of two criteria.

    ID      Date1       Date2       Product
    101     1/1/2016    5/30/2016   Bond    
    101     1/1/2016    5/30/2016   Swap    
    101     1/1/2016    5/30/2016   Swap
    201     2/28/2016   7/1/2016    Swap
    201     2/28/2016   7/1/2016    Swap
    201     2/28/2016   7/1/2016    Swap
    

    If ColumnD is a Bond, fetch the corresponding Date1 and if it’s a Swap, fetch the corresponding Date2. The thing is, I have to look for a change in the ID, because this is a pool and I want to do some lookups, or index/match, based on each pool. Whenever the ID changes, I need to check the Bond or Swap, and pick the appropriate date.

    The Bond is primary here, so if a Bond exists for a pool, use Date1 and if no Bond for the pool, use Date2.

    I want to use a combination of functions, if possible, and not VBA.

    Can someone give me a suggestion here?


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Tuesday, January 26, 2016 2:02 PM

Answers

  • F1:  ID
    F2:  Product
    F4:  Date1
    F5:  Date2
    F7:  Result-Date
    G1:  201
    G2:  Bond
    G4:  {=INDEX(B:B,MATCH(G1&"Bond",A:A&D:D,0))}
    G5:  {=INDEX(C:C,MATCH(G1&"Swap",A:A&D:D,0))}
    G7:  =IF(ISERROR(G4),G5,G4)

    Means get the dates for both, choose the 2nd date if 1st is not found.

    Andreas.

    Tuesday, January 26, 2016 4:07 PM