I am in an awful pickle trying to fathom a way to return the most recently entered data that complies with several criteria.
Here is a sample from my rather large table (apologies if my sample is too big):
A B C D E
Case Ref Date Pay/Res Operation Amount
1 A 01/05/11 Reserve Settlement $6000
2 A 05/06/12 Reserve Settlement $3000
3 A 01/05/11 Reserve Lawyer $5000
4 A 02/02/12 Reserve Lawyer $2500
5 A 12/04/12 Reserve Lawyer $4000
6 A 05/06/12 Reserve Lawyer $2000
7 B 14/07/11 Reserve Settlement $5000
8 B 14/07/11 Reserve Lawyer $12000
9 B 15/09/11 Reserve Lawyer $9000
10 B 23/12/11 Reserve Lawyer $6000
11 B 03/04/12 Reserve Lawyer $3000
12 B 05/07/12 Reserve Lawyer $0
13 C 10/10/11 Reserve Settlement $2000
14 C 06/08/12 Reserve Settlement $10000
15 C 01/10/11 Reserve Lawyer $7500
16 C 04/01/12 Reserve Lawyer $5000
17 C 06/03/12 Reserve Lawyer $2500
18 C 10/10/11 Reserve Misc. $4000
19 D 06/04/12 Reserve Lawyer $1000
20 E 01/03/12 Reserve Settlement $5000
21 E 04/08/12 Reserve Settlement $0
22 E 01/03/12 Reserve Lawyer $1000
23 E 04/08/12 Reserve Lawyer $0
I have named the data in each of the above columns, in a bid a simplify calculations (ref; date; payres;type;amt)
I am looking for a formula that will complete the most recent information entered in a master sheet. For example, in the cell that corresponds to claim A, Reserve Settlement, the value returned should be $3000, as this is the most recently entered figure that corresponds to ref="A", payres="Reserve", type="Settlement".
The figure should not automatically be either the MAX or MIN value, as reserves are prone to fluctuate. For example, the Reserve Lawyer information for claim A drops and then rises again, before dropping anew, whilst the settlement reserve for claim C is initially set too low and is later adjusted upwards.
I have tried all manner of formulas mixing and matching VLOOKUP, INDEX, MATCH, SUMPRODUCT etc. etc., but have thus far had no luck.
Any suggestions would be most gratefully received.
Thanks.
Bookmarks