Find value matching multiple text criteria and max date

You can write below formula for Category A & Currency USD

=OFFSET(INDIRECT(ADDRESS(SUM((MAX((B2:B6="A")*(C2:C6="USD")*(A2:A6))=(B2:B6="A")*(C2:C6="USD")*(A2:A6))*ROW(A2:A6)),1)),0,3)

In attached sheet i've written formula for referenced cells as well.

If the dates are sorted in Ascending order (key) you could use LOOKUP

If we assume your example data were in A1:D6 with row 1 being headers then the <formula> in D6 would be:

=LOOKUP(2,1/(($B$1:$B5=$B6)*($C$1:$C5=$C6)),$D$1:$D5)

@ DK very thought full approach but optional with dates .....

Forum missed you a lot and many post went on other pages without replying as you were not there ... This post was in my mind and i had the file with me but had idea that you must be get some short solution of the problem ....

You can put formula in separate columns except A to C and extend your range upto 1000 ................

=OFFSET(INDIRECT(ADDRESS(SUM((MAX((INDIRECT("$C$2:$C$"&ROW($C7))=$C8)*(INDIREC

T("$A$2:$A$"&ROW($A7))))=(INDIRECT("$C$2:$C$"&ROW($C7))=$C8)*(INDIRECT("$A$2:$A$"&ROW($A7))))*ROW(INDIRECT("$A$2:$A$"&ROW($A7)))),1)),0,3)

I also worked in your formula and instead of using row($c7) you can use Row()-1 INDIRECT("$C$2:$C$"&ROW()-1) but Me too astonished its not working .... Instead of this if you put INDIRECT("$C$2:$C$"&6-1) then its working .....

i think DK or some expert can put some light on it that why INDIRECT("$C$2:$C$"&ROW()-1) is not working .....

I confess I don't really understand the question exactly... it's also not clear to me if the date should be restricted based upon the date in Column A of the current row, for ex. in row 8 given the date is 3rd May should the value applied be the last assigned to the same currency before or on the date of A8 ?

ie you could use shorter (non volatile) array of:

D8:
=INDEX($D$2:$D7,MATCH(MAX(IF($C$2:$C7=$C8,$A$2:$A7))&$C8,$A$2:$A7&$C$2:$C7,0))
committed with CTRL + SHIFT + ENTER

this would return 25 as it will return the value of D5 (the rate assigned to the MAX date where USD is currency), however, the date in A5 exceeds that of A8... is that correct ?

I would suggest providing a slightly larger sample if possible clearly denoting desired results entered manually - working off small datasets makes it difficult to determine logic etc...

In my attached Sheet you can see formulas. Although we can adopt other ways as well but i'm very much confused with the behavior of Row() function in below formula's. Although its working with single formula.

=MAX($D$2:$D$6)
=MAX(INDIRECT("$D$2:$D$"&7-1))
=MAX(INDIRECT("$D$2:$D$"&ROW()-8)) (i'm on row 14 so subtract -8

All above formula's all working and now in our formula's ...

These two working properly

=OFFSET(INDIRECT(ADDRESS(SUM((MAX(($B$2:$B$6=A9)*($C$2:$C$6=B9)*($A$2:$A$6))=($B$2:$B$6=A9)*($C$2:$C$6=B9)*($A$2:$A$6))*ROW($A$2:$A$6)),1)),0,3)

=OFFSET(INDIRECT(ADDRESS(SUM((MAX((INDIRECT("$B$2:$B$"&7-1)=A9)*($C$2:$C$6=B9)*($A$2:$A$6))=($B$2:$B$6=A9)*($C$2:$C$6=B9)*($A$2:$A$6))*ROW($A$2:$A$6)),1)),0,3)

But when i add Row() function it gives error

=OFFSET(INDIRECT(ADDRESS(SUM((MAX((INDIRECT("$B$2:$B$"&ROW()-3)=A9)*($C$2:$C$6=B9)*($A$2:$A$6))=($B$2:$B$6=A9)*($C$2:$C$6=B9)*($A$2:$A$6))*ROW($A$2:$A$6)),1)),0,3)

Although there must be some proper reason but i'm jammed here ...

Rolf, in your latest sample that the transactions are in fact sorted by date... you implied this was not the case before... if you can sort by date do so as you can (perhaps) avoid Arrays altogether... we will know later I guess.

Attached is your file reworked - to avoid circular referencing I revised your formulae in J and created a new column K to return definitive results, and set H such that it always displayed the rate to be used (ie use Real when available else calculate rate)... I've assumed that the rate should be determined by the last rate for a given currency up to and including the given date ... it's also not clear if the rate should be determined not only by currency but also by Trx Type... I would suggest that if you need to post a further sample provide a column of manually entered expected results... this way we can at least try to follow the logic - as is it's quite difficult - this problem may seem obvious to yourself given you're working on it but for those who spend perhaps a max of 5 mins looking at it can be much harder to decipher.

krunk, take a moment to read the Forum Rules (link above), they will help you a lot in getting fast answers.

Your question needs to be posted into a thread of its own where all the forum contributors might see it and respond, whereas this old thread only a couple of people are subscribed to it and "might" respond eventually.

New threads have the greatest number of possible helpers view them. Thanks.