Maximum Number and Date

[Kenshin]

Glad Im here again and desperate need s help from you guys, please take a look at the file



tghank you in advanced

 

[p45cal]

In cell C11:
=MAX(INDEX(B3:K6,MATCH(C9,A3:A6,0),0))

In cell D11:
=INDEX(A2:K2,MAX(IF(INDEX(B3:K6,MATCH(C9,A3:A6,0),0)=C11,COLUMN(B2:K2))))
or better, especially if your dates aren't sorted left to right:
=MAX(IF(INDEX(B3:K6,MATCH(C9,A3:A6,0),0)=C11,B2:K2))
These last two formulae may need to be entered with Ctrl+Shift+Enter, not just plain Enter, depending on your version of Excel.

 

[Kenshin]

Thank you @p45cal it works, thank you man

 

[Kenshin]

Sorry @p45cal need help again, I try the formula on my actual workbook and the formula failed to do htheir works

 

[p45cal]

=INDEX(A5:AG5,MAX(IF(INDEX(C6:AG105,MATCH(B119,B6:B105,0),0)=D120,COLUMN(C5:AG5))))
but better:
=MAX(IF(INDEX(C6:AG105,MATCH(B119,B6:B105,0),0)=D120,C5:AG5))

 

[Kenshin]

Thats for date? Yes it works but how for maximum number? Still address wrong maximum number

 

[p45cal]

In cell U6 there is 800000000!

 

[Kenshin]

Oh sorry I misunderstood that the result should be the last number from multiple columns with criteria not maximum number
Still need your help sir how to get the last number and date using criteria

thank you @p45cal

 

[p45cal]

Date:
=INDEX(A5:AG5,MAX(IF(ISNUMBER(INDEX(C6:AG105,MATCH(B119,B6:B105,0),0)),COLUMN(C5:AG5))))

Amount:
=INDEX(A6:AG105,MATCH(B119,B6:B105,0),MAX(IF(ISNUMBER(INDEX(C6:AG105,MATCH(B119,B6:B105,0),0)),COLUMN(C5:AG5))))

 

[Peter Bartholomew]

 

[bosco_yip]

1] Last date [C119] :

=OFFSET(B5,,INDEX(MATCH(2,1/OFFSET(C5,MATCH(B119,B6:B105,0),,,31)),0))

2] Last value [D119] :

=1/LOOKUP(2,1/OFFSET(C5,MATCH(B119,B6:B105,0),,,31))

 

[Kenshin]

Thank you @p45cal , @Peter Bartholomew and @bosco_yip , thank you Ninjas Godbless you All