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

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

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
 
S

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top