Return most recently entered amount based on look up of several criteria

Originally Posted by NBVC

Try:

=LOOKUP(2,1/INDEX(($A$2:$A$100=X1)*($B$2:$B$100=X2),0),$C$2:$C$100)

where A2:C100 contains the data and X1, X2 contain lookup variables, A and Reserve Settlement....

Awesome!!!!

That works a treat! I have managed to make it work with if(isna... so that those cases where one or more types of Reserve would otherwise return #N/A instead show 0.

I'm chuffed to bits (and can now stop pulling my hair out).

Many thanks indeed for your kind assistance

Originally Posted by NBVC

You are welcome...

You probably don't really need the extra INDEX function in there:

=LOOKUP(2,1/(($A$2:$A$100=X1)*($B$2:$B$100=X2)),$C$2:$C$100)

I've just checked that and you are quite right!

I see that it returns the last entered value in respect of each claim + type of reserve. Is there any way to build in to the formula something to ensure that the value returned always corresponds to the most recent date (in case, for example, some joker entering information manages to put the dates in reverse order)?

Just so you can see what I have done to your tidy formula thus far:

=IF(ISNA(LOOKUP(2,1/((ref=$H7)*(payres="Reserve")*(type="Settlement")),amt)),0,LOOKUP(2,1/INDEX((ref=$H7)*(payres="Reserve")*(type="Settlement"),0),amt))

I'm still wondering about my query regarding the actual dates entered, as the formula you kindly provided, NBVC, seems to give me the last entry when reading down the page, but does not give the last entry by date if I tamper with the data and put the dates in jumbled order.

Any further guidance you may be able to provide would be very highly appreciated!

Originally Posted by NBVC

Try:

Please Login or Register  to view this content.

where date is named range for dates column.

This formula is an Array Formula and must be confirmed with CTRL+SHIFT+ENTER not just ENTER.

Also note the LOOKUP(10^10,CHOOSE({1,2},0, construct is an error handler. This returns 0 if no matches found. It reduces the amount of resources needed to calculate the same array function twice when using your nested if version. If you have Excel 2007 or later, you can use IFERROR() instead.

Many thanks, NBVC.

I have just tried your suggestion (and made sure it was entered as an array formula), but the results are rather different than hoped for!!

I applied it to a different section of my sheet, where payres contains entries that are not only Reserve, but also Payment and where type has four variables (Settlement, Lawyer, Assessor, Misc.).

For some reason, the formula returns the first entered value in my list, regardless of whether I want to look up Payment : Settlement or Payment : Lawyer etc. For Reserve, the formula appears to work as planned, unless a certain type of Reserve, e.g. Reserve : Assessor, is not used, in which case the first value at the top of the sheet is returned.

I suspect that this glitch may have something to do with my perhaps not expressing adequately the need to distinguish between Payment and Reserve (due to the fact that my sample sheet does not show any Payment occurrences). My apologies.

This is getting interesting and I would very much appreciate any further tricks you may have up your sleeve to solve this conundrum!

Thanks!

(if it would help, I could attach an actual section of my sheet - let me know).

Hi NBVC!

I've tidied up my sheet and made it anonymous, but would like to know if there are any specific things I should do to it prior to attaching here, to protect the identity of my employer etc. Please advise!

Thanks.

I wanted to provide a solution for a similiar scenario I was in recently. I was trying to figure out the maximum date given conditions, same as you.
The following ARRAY formula entered in H2 would get you the newest date ( assuming f2=Pas/Res Option, g2= Operation Option): =MAX(($c$2:$c$23=f2)*($d$2:$d$23=g2)*($b$2:$b$23))
you then could use a helper column to create unique values for each entry (=c2&d2&b2 copied down) and use a vlookup using the the unique value you have created (=(f2&g2&h2)).
Sorry if this isn't clear, and you might not need it, it just worked well for me, and I BELIEVE it may fix the issue you are having, so I'd provide you with another option. Let me know if this is of use to you.

Hi NBVC,

I didn't have much luck trying to upload an attachment, so here is the longhand version, inconveniently deformatted:

A B C D E

Case Ref Date Pay/Res Operation Amount


G-147 06 07 29 Payment Lawyer $1,494.45
G-147 07 04 24 Payment Lawyer $3,528.43
G-147 07 11 27 Payment Lawyer $2,073.74
G-147 08 02 16 Payment Lawyer $3,452.03
G-147 08 09 12 Payment Lawyer $1,521.68
G-147 09 02 03 Payment Lawyer $34.39
G-147 10 03 02 Payment Lawyer $1,662.21
G-147 10 03 02 Payment Lawyer $1,315.04
G-147 10 03 30 Payment Lawyer $1,981.64
G-147 10 07 28 Payment Lawyer $1,620.90
G-147 11 01 15 Payment Lawyer $2,605.97
G-147 11 03 01 Payment Lawyer $1,241.91
G-147 11 04 21 Payment Lawyer $1,242.99
G-147 11 07 13 Payment Lawyer $866.87
G-147 11 08 25 Payment Lawyer $5,435.42
G-147 11 12 08 Payment Lawyer $4,910.23
G-147 12 01 27 Payment Lawyer $1,250.15
G-147 12 03 29 Payment Lawyer $761.20
G-147 12 08 01 Payment Lawyer $926.33
G-147 07 04 24 Payment Misc. $250.43
G-147 10 03 30 Payment Misc. $1,664.25
G-147 11 02 03 Payment Misc. $2,705.35
G-147 11 10 05 Payment Misc. $2,108.21
G-147 11 11 11 Payment Misc. $921.02
G-147 11 11 11 Payment Misc. $62.70
G-147 11 11 24 Payment Misc. $1,826.55
G-147 11 12 08 Payment Misc. $106.27
G-147 11 12 08 Payment Misc. $924.02
G-147 11 12 08 Payment Misc. $1,807.17
G-147 12 01 27 Payment Misc. $2,507.47
G-147 12 02 08 Payment Misc. $1,432.84
G-147 12 02 22 Payment Misc. $49.80
G-147 12 06 29 Payment Misc. $2,564.77
G-147 06 06 08 Reserve Settlement $95,100.00
G-147 06 09 05 Reserve Lawyer $1,317.12
G-147 06 12 06 Reserve Lawyer $6,340.00
G-147 07 03 08 Reserve Lawyer $2,887.97
G-147 07 06 08 Reserve Lawyer $1,366.29
G-147 08 06 07 Reserve Lawyer $1,331.90
G-147 08 09 05 Reserve Lawyer $5,059.40
G-147 08 12 04 Reserve Lawyer $3,077.75
G-147 09 03 04 Reserve Lawyer $1,456.85
G-147 09 06 02 Reserve Lawyer $11,438.09
G-147 09 08 31 Reserve Lawyer $10,202.26
G-147 09 11 29 Reserve Lawyer $12,680.00
G-147 10 02 27 Reserve Lawyer $7,769.77
G-147 10 05 28 Reserve Lawyer $6,551.86
G-147 10 08 26 Reserve Lawyer $5,790.66
G-147 10 11 24 Reserve Misc. $3,634.65
G-147 11 02 22 Reserve Misc. $3,170.00
G-147 11 05 23 Reserve Misc. $921.02
G-147 11 08 21 Reserve Misc. $3,170.00
G-147 11 11 19 Reserve Misc. $1,737.16
G-147 12 02 17 Reserve Misc. $1,687.36
G-147 12 05 17 Reserve Misc. $2,507.23

I have this little check table:

G H I J K L

1 Case Ref Pay/Res Settlement Assessor Lawyer Misc.
2 G-147 Payment $1,494.45 $1,494.45 $926.33 $2,564.77
3 G-147 Reserve $95,100.00 $1,494.45 $5,790.66 $2,507.23

This is the formula as inserted in H2 (and dragged to cover up to L3):

=LOOKUP(10^10,CHOOSE({1,2},0,INDEX(amt,MATCH(TRUE,MAX(IF((ref=$G$2)*(payres=$H2)*(type=I$1),date))=(ref=$G$2)*(payres=$H2)*(type=I$1)*(date),0))))

The results shown are as calculated, but they should be thus:

G H I J K L

1 Case Ref Pay/Res Settlement Assessor Lawyer Misc.
2 G-147 Payment $0.00 $0.00 $926.33 $2,564.77
3 G-147 Reserve $95,100.00 $0.00 $5,790.66 $2,507.23

As you can see, when there is no corresponding entry, the formula returns the number at the top of the sheet.

I have just given one case ref, so as to spare you a very long list indeed!

Have fun! Many thanks for taking your time to consider this query.

I think you've nailed it, NBVC! - Many thanks indeed!! :D

Thanks also, Melvinrobb. I gave it a try, but think I'll stick with the solution that NBVC conjured up, so as to keep to a minimum of columns.