Help with Index/Match Formula

tunibell

New Member
Joined
Jun 13, 2015
Messages
3
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 DateEnd DateBenefit Type
1017/1/148/31/14None
1019/1/14Employee Only
1027/1/14Employee +1
1037/1/14None

<tbody>
</tbody>

And this is what the calculation results should look like:

Employee ID#Pay DateBenefit Type
1019/30/14Employee Only
1029/30/14Employee +1
1039/30/14None

<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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
How do you arrive at your answer? I think you have not shared all your info with us?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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))
 
Upvote 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!
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top