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!

On Wednesday, August 8, 2012 3:55:22 AM UTC-5, Simon Whight wrote:
> 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 theboundary, 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!

Send this msg and before/after examples FILE to dguillett @gmail.com

if youd like you can send me the workbook to take a look at.

nickmcd @ gmail

On Wednesday, August 8, 2012 1:26:15 PM UTC+1, Don Guillett wrote:
> On Wednesday, August 8, 2012 3:55:22 AM UTC-5, Simon Whight wrote:
>
> > 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. Itneeds 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 onsolve other similar issues.
>
> >
>
> >
>
> >
>
> > Thanks for any help!
>
>
>
> Send this msg and before/after examples FILE to dguillett @gmail.com

Ok both, I'll send a slightly edited version of the book otherwise I will get a shouting from work!

On Thursday, August 9, 2012 3:15:27 AM UTC-5, Simon Whight wrote:
> On Wednesday, August 8, 2012 1:26:15 PM UTC+1, Don Guillett wrote:
>
> > On Wednesday, August 8, 2012 3:55:22 AM UTC-5, Simon Whight wrote:
>
> >
>
> > > 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 thingto 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 withinthe 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 notsure 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!
>
> >
>
> >
>
> >
>
> > Send this msg and before/after examples FILE to dguillett @gmail.com
>
>
>
> Ok both, I'll send a slightly edited version of the book otherwise I willget a shouting from work!

Didn't get it