Help with an array formula

barkingfrog

New Member
Joined
Jan 18, 2005
Messages
4
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.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Care to describe the task (without the formulas) with a judicious sample of data along with the desired results?
 

barkingfrog

New Member
Joined
Jan 18, 2005
Messages
4
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
barkingfrog said:
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

New Member
Joined
Jan 18, 2005
Messages
4

ADVERTISEMENT

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

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
barkingfrog said:
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... :LOL:
Book3
ABCDE
1ClientAccidentDatePolicyYearStartEnd
2Client-A7/31/20032003/0413
3Client-A1/1/20042003/0413
4Client-B1/1/20042004/0547
5Client-C3/31/20042003/04810
6Client-B3/31/20042004/0547
7
8
9
10ClientPolicyinceptionExpiryPolicyYear
11Client-A7/1/20026/30/20032002/03
12Client-A7/1/20036/30/20042003/04
13Client-A7/1/20046/30/20052004/05
14Client-B1/1/200212/31/20022002/03
15Client-B1/1/200312/31//20032003/04
16Client-B1/1/200412/31/20042004/05
17Client-B1/1/200512/31/20052005/06
18Client-C4/1/20023/31/20032002/03
19Client-C4/1/20033/31/20042003/04
20Client-C4/1/20043/31/20052004/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)
 

Forum statistics

Threads
1,148,530
Messages
5,747,238
Members
424,070
Latest member
smanni3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top