# Index Match with a range

1. ## 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. ## 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. ## 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. ## 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)))))}

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

#### 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