# INDEX/MATCH -> having issues getting to grips with it

simon.whight@gmail.com
Guest
Posts: n/a

 8th Aug 2012
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!

Don Guillett
Guest
Posts: n/a

 8th Aug 2012
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

nick.mcd@gmail.com
Guest
Posts: n/a

 8th Aug 2012
if youd like you can send me the workbook to take a look at.

nickmcd @ gmail

Simon Whight
Guest
Posts: n/a

 9th Aug 2012
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!

Don Guillett
Guest
Posts: n/a

 9th Aug 2012
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

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Catherine Microsoft Word Document Management 4 25th Feb 2008 05:18 AM Jez Microsoft Outlook 5 19th Aug 2005 04:35 AM Jerry Spence1 Microsoft VB .NET 0 28th May 2005 05:20 PM =?Utf-8?B?Q2FubmEgVw==?= Windows XP Photos 5 4th Nov 2004 07:04 PM Jordan Storage Devices 3 27th Sep 2004 12:33 AM

Features