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

[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!

 

[Don Guillett]

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]

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

nickmcd @ gmail

 

[Simon Whight]

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]

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

Didn't get it