Help with an array formula

[barkingfrog]

I’m trying to create a formula that would be capable of returning a value based on two criteria. The second of which is a date range, this is where my problem lies. I can use INDEX to return a value of a fixed criteria but not a range as I can by using VLOOKUP. I need something that can combine both and I’m struggling for a solution, so any help would be appreciated.

In basic terms, I have a list of Insurance clients in column A and a list of accident dates in column B, I need to return the policy year based on that accident date, those policy periods will change for different clients. Given one client and several accident dates, I can just use the following VLOOKUP to a table on another worksheet:- =VLOOKUP(B2,Lookups!$A$2:$B$21,2) to return the pol year.

The Table being so:-

DATE POL YEAR
21/06/1990 1990-1991
21/06/1991 1991-1992
21/06/1992 1992-1993
21/06/1993 1993-1994
21/06/1994 1994-1995
21/06/1995 1995-1996
21/06/1996 1996-1997
21/06/1997 1997-1998
21/06/1998 1998-1999
21/06/1999 1999-2000
21/06/2000 2000-2001
21/06/2001 2001-2002
21/06/2002 2002-2003
21/06/2003 2003-2004
01/04/2004 2004-2005
01/04/2005 2005-2006
01/04/2006 2006-2007
01/04/2007 2007-2008
01/04/2008 2008-2009
01/04/2009 2009-2010

However, if I have Client A,B,C,D each with different insurance periods, I have to first match the client before the lookup. If it was just one accident date, I could use an INDEX array formula in column F:-

=INDEX($C$2:$C$5,MATCH(D2,IF($B$2:$B$5=E2,$A$2:$A$5),0))



Client Accident Date Policy Year Match ClientMatch Acc Result
Client A 01/01/1990 2001 Client A 01/01/1990 2001
Client B 01/01/1991 2002 Client B 01/01/1991 2002
Client C 01/01/1992 2003 Client C 01/01/1992 2003
Client D 01/01/1993 2001 Client D 01/01/1993 2004

Is it possible to change this to a range. Failing this I can only think of having a separate worksheet for each client with the lookup table in the same range on each sheet and creating a Macro that does a find and then a lookup. Many thanks for any help given.

 

[Aladin Akyurek]

Care to describe the task (without the formulas) with a judicious sample of data along with the desired results?

 

[barkingfrog]

This represents the data I have with the year in C being the formula result.

Client Accident Date Year (Result)
Client A 31/07/2003 2003/04
Client A 01/01/2004 2003/04
Client B 01/01/2004 2004/05
Client C 31/03/2004 2003/04
Client B 31/03/2004 2004/05



Below are the look-up values. The point is that we could insure any given client over several years and clients can have differing incepting expiry periods of policy cover. Therefore it's necessary to first establish the Client and then when their policy periods in order to bring back the appropriate policy year that the accident (date) relates too.

Client Policy inception Expiry Policy Year
Client A 01/07/2002 30/06/2003 2002/03
Client A 01/07/2003 30/06/2004 2003/04
Client A 01/07/2004 30/06/2005 2004/05
Client B 01/01/2002 31/12/2002 2002/03
Client B 01/01/2003 31/12/2003 2003/04
Client B 01/01/2004 31/12/2004 2004/05
Client B 01/01/2005 31/12/2005 2005/06
Client C 01/04/2002 31/03/2003 2002/03
Client C 01/04/2003 31/03/2004 2003/04
Client C 01/04/2004 31/03/2005 2004/05

 

[Aladin Akyurek]

This represents the data I have with the year in C being the formula result.

Client Accident Date Year (Result)
Client A 31/07/2003 2003/04
Client A 01/01/2004 2003/04
Client B 01/01/2004 2004/05
Client C 31/03/2004 2003/04
Client B 31/03/2004 2004/05



Below are the look-up values. The point is that we could insure any given client over several years and clients can have differing incepting expiry periods of policy cover. Therefore it's necessary to first establish the Client and then when their policy periods in order to bring back the appropriate policy year that the accident (date) relates too.

Client Policy inception Expiry Policy Year
Client A 01/07/2002 30/06/2003 2002/03
Client A 01/07/2003 30/06/2004 2003/04
Client A 01/07/2004 30/06/2005 2004/05
Client B 01/01/2002 31/12/2002 2002/03
Client B 01/01/2003 31/12/2003 2003/04
Client B 01/01/2004 31/12/2004 2004/05
Client B 01/01/2005 31/12/2005 2005/06
Client C 01/04/2002 31/03/2003 2002/03
Client C 01/04/2003 31/03/2004 2003/04
Client C 01/04/2004 31/03/2005 2004/05

Is the 2nd table sorted on Client as the sample implies? If not, would you be willing to sort it on Client?

 

[barkingfrog]

Yes the table is sorted by Client, this is not a problem. I can sort both the look-up table and the data by any value needed. Can I just say that we are talking many, many clients here so it is not possible to use a nested IF function to match individual clients, then proceed to the appropriate look-up range as there are too many.

 

[tusharm]

Assume that the policy table is in Sheet1 starting with cell A1 (row 1 is a header) and the accident information is in Sheet2 starting with A1 (row 1 is again a header).
Then, in Sheet2, use the array formula:

=INDEX(Sheet1!$D$2:$D$11,MATCH(1,(A2=Sheet1!$A$2:$A$11)*(Sheet2!B2>=Sheet1!$B$2:$B$11)*(Sheet2!B2<=Sheet1!$C$2:$C$11),0))

Note that you may be better off defining the ranges containing data with a named formula that makes the result automatically adjust to a changing data set. Along the lines of the suggestions in 'Dynamic Charts' (http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html)

 

[Aladin Akyurek]

Yes the table is sorted by Client, this is not a problem. I can sort both the look-up table and the data by any value needed.

Terrific.

Can I just say that we are talking many, many clients here so it is not possible to use a nested IF function to match individual clients, then proceed to the appropriate look-up range as there are too many.

I don't recall that conversation...

Book3
	A	B	C	D	E
1	Client	AccidentDate	PolicyYear	Start	End
2	Client-A	7/31/2003	2003/04	1	3
3	Client-A	1/1/2004	2003/04	1	3
4	Client-B	1/1/2004	2004/05	4	7
5	Client-C	3/31/2004	2003/04	8	10
6	Client-B	3/31/2004	2004/05	4	7
7
8
9
10	Client	Policyinception	Expiry	PolicyYear
11	Client-A	7/1/2002	6/30/2003	2002/03
12	Client-A	7/1/2003	6/30/2004	2003/04
13	Client-A	7/1/2004	6/30/2005	2004/05
14	Client-B	1/1/2002	12/31/2002	2002/03
15	Client-B	1/1/2003	12/31//2003	2003/04
16	Client-B	1/1/2004	12/31/2004	2004/05
17	Client-B	1/1/2005	12/31/2005	2005/06
18	Client-C	4/1/2002	3/31/2003	2002/03
19	Client-C	4/1/2003	3/31/2004	2003/04
20	Client-C	4/1/2004	3/31/2005	2004/05
Sheet1

Formulas...

C2, copied down:

=LOOKUP(B2,INDEX($B$11:$B$20,D2):INDEX($D$11:$D$20,E2))

D2, copied down:

=MATCH(A2,$A$11:$A$20,0)

E2, copied down:

=MATCH(A2,$A$11:$A$20,1)

 

[barkingfrog]

Thank you both, absolute genius!