Help with Index/Match Formula

tunibell

New Member
I need help creating a formula that will respond with benefit information that matches an ID number and falls between two dates. For example, here would be the reference chart:

 Employee ID # Start Date End Date Benefit Type 101 7/1/14 8/31/14 None 101 9/1/14 Employee Only 102 7/1/14 Employee +1 103 7/1/14 None

<tbody>
</tbody>

And this is what the calculation results should look like:

 Employee ID# Pay Date Benefit Type 101 9/30/14 Employee Only 102 9/30/14 Employee +1 103 9/30/14 None

<tbody>
</tbody>

All my previous attempts seem to return the first value in the reference table for all the cells.

Help much appreciated!

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
How do you arrive at your answer? I think you have not shared all your info with us?

How do you arrive at your answer? I think you have not shared all your info with us?

That's the problem...I can't get the answer I'm looking for. The second table, third column shows what I'd like the answer to be.

Yes, I get that is what you want the answers to be, but how do you get them in the 1st place? How would you do this calc manually?

Yes, I get that is what you want the answers to be, but how do you get them in the 1st place? How would you do this calc manually?

The values in the first table are just entered directly. They aren't pulling from anything else.

FDibbins is asking you what exactly do you want to return. I'm assuming you want to return the "Benefit Type" that matches the ID number where the Pay Date is between the Start Date and End Date. Is that correct? However your table does not have End Dates for all the data.

Assuming your first table is in columns A through D... and your reference table is in columns F through H... you could enter this array formula (CTRL+SHIFT+ENTER) in cell H2 and fill down.

=INDEX(D:D,MATCH(1,IF(G2>=B:B,IF(G2<=C:C,IF(F2=A:A,1))),0))

Oops! Forgot to mention this will only work if your table has End Dates. You're going to have to add another condition somewhere if you want to leave some End Dates blank. I don't have enough time maybe someone else could figure that part out. Cheers!

Replies
3
Views
288
Replies
4
Views
500
Replies
6
Views
361
Replies
1
Views
211
Replies
2
Views
247

1,196,113
Messages
6,013,551
Members
441,770
Latest member
Griggsy28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back