Forum Discussion
Solved
Return value based on multiple critieria
Hi, I'm trying to return the WSR rule (Work Schedule Roster) that applied to an individual on a given date. The first table is my expected result for the given values in the data table. Pers...
DPifarre Please see the attached file. Your data will stay in a table named Data on Tab Data and the result of look up will be on Tab Output. Note that I have corrected your End date to the year 9999 instead of 1999 as currently.
I use Index/Match array formula to look up. Basically, it will look at the Data Table, try to find the intersection of column 4 where your WS Rule stays and the row where all conditions of (Person ID, Start date <= your lookup date, End date >= your look up date) are satisfied.
Jos_Woolley That explains why my Excel kept frozen when evaluating the formula. I also try to avoid referring a whole column/row as much as possible in any formula. Thanks for your explanation.
