S
simon.whight
Got a scenario where a vlookup is too restrictive for what I am doing. Pinned the solution as an INDEX/MATCH combination but can't get the thing to work as it is my first attempt with this formula.
Tried working through with online help to no avail, so thought I'd ask the crowd for some assistance directly relational to my problem.
We'll simplify my data to just what I am looking for.
I have two sheets. Sheet 1:
Column A - contains a UID
Column B - contains a date
other columns - financial data and various
Sheet 2:
Column A - contains a list of the UID
Column B - contains a FROM date
Column C - contains a TO date
Column D - contains a calculation basis definition
What I am trying to do is get the appropriate calculation basis definition onto Sheet 1. It must be the right definition for the relevant period.
Vlookup is easy where there are not multiple lines of the UID, it is the need to pick the relevant one for the period that is causing my issue. It needs to find the right UID, check that the date applicable falls within the boundary, and then bring back the calculation definition to the other sheet.I'm sure it is this latter bit that I am falling down on as I am not sure on the syntax.
I've not decided whether the TO date is to be blank or "far future date", so we are flexible there.
Does that make sense? Hopefully a solution to this will help me push on solve other similar issues.
Thanks for any help!
Tried working through with online help to no avail, so thought I'd ask the crowd for some assistance directly relational to my problem.
We'll simplify my data to just what I am looking for.
I have two sheets. Sheet 1:
Column A - contains a UID
Column B - contains a date
other columns - financial data and various
Sheet 2:
Column A - contains a list of the UID
Column B - contains a FROM date
Column C - contains a TO date
Column D - contains a calculation basis definition
What I am trying to do is get the appropriate calculation basis definition onto Sheet 1. It must be the right definition for the relevant period.
Vlookup is easy where there are not multiple lines of the UID, it is the need to pick the relevant one for the period that is causing my issue. It needs to find the right UID, check that the date applicable falls within the boundary, and then bring back the calculation definition to the other sheet.I'm sure it is this latter bit that I am falling down on as I am not sure on the syntax.
I've not decided whether the TO date is to be blank or "far future date", so we are flexible there.
Does that make sense? Hopefully a solution to this will help me push on solve other similar issues.
Thanks for any help!