+ Reply to Thread
Results 1 to 4 of 4

Index Match with a range

  1. #1
    Forum Contributor
    Join Date
    05-02-2017
    Location
    NoVa, United States
    MS-Off Ver
    2010
    Posts
    107

    Index Match with a range

    Okay I have a bit of a doozy. I need to pull in the cost center one sheet onto another sheet. The trick is that it needs to be the closest date to the effective date without going over AND it needs to be the highest sequence number.

    I've attached a test worksheet. The EE tab needs to pull in the cost center from the Job tab. I've input the expected result in the cost center column on the EE tab.

    I think the key is going to be something like this. I'm just not sure how to work it all out with the sequences.

  2. #2
    Forum Contributor
    Join Date
    05-02-2017
    Location
    NoVa, United States
    MS-Off Ver
    2010
    Posts
    107

    Re: Index Match with a range

    I'm thinking it's going to be something like this, but my index match match skills are really not up to par:

    {=INDEX(Job!E:E,MATCH(1,IF(C3>=Job!D:D,IF(C3<=Job!D:D,1)),0),MATCH(EE!A3,Job!A:A,0))}

    AND this doesn't take the sequence number into consideration.

  3. #3
    Forum Contributor
    Join Date
    05-02-2017
    Location
    NoVa, United States
    MS-Off Ver
    2010
    Posts
    107

    Re: Index Match with a range

    The problem with my first formula is obviously the competing greater than/less than statements. However, if I just do less than or equal, I don't come up with the right value.
    So, for row 3 (Vielka Omar Vang - 3/20/19), what I expect is the cost center 9900100 (because that's what it was on 3/1/2019), however I obviously get the value for the earliest effective date (1010100).

    {=INDEX(Job!E:E,MATCH(A3&1,Job!A:A&IF(C3<=Job!D:D,1)),0)}

    I can probably do a sort to get rid of the necessity for figuring out how to get the highest sequence number.

  4. #4
    Forum Contributor
    Join Date
    05-02-2017
    Location
    NoVa, United States
    MS-Off Ver
    2010
    Posts
    107

    Re: Index Match with a range

    Okay, I think I figured it out.
    First, I have to sort the 'Job' tab.
    Sort by ID
    Then Eff Date (oldest to newest)
    Then Sequence (smallest to largest)

    Then in the EE tab, the formula should be:

    {=INDEX(Job!F:F,MAX(IF(Job!A:A=EE!A2,IF(Job!D:D<=EE!C2,ROW(Job!F:F)))))}

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1