Get latest value from table based on multiple criteria

[matt0020190]

Hi all

This one is driving me mad.

Can anyone suggest a formula that will always get the latest value (and ignore blank values) from a table?

Week -----Period ------Value
1                   1               -£377
2                   1                £219
3                   1                £759
4                   1               -£1,106
1                   1                £302
2                   1                £409
3                   1                -£160
4                   1                -£151
1                   1
2                   1                 £0
3                   1                 £45
4                   1

In the above data, I am after a formula that will always display the latest value based on two conditions and ignore blank values. E.g

If week = 4 & Period = 1, latest value to return is = -£151

I have managed to get an index match and lookup formula working for this need, however they both include the blank cells. I want the formula to ignore blank or zero values. 

Any ideas please?

Many thanks

[#2]

@matt0020190 

Let's say you enter the week (4) in E2 and the Period (1) in F2.

Formula in G2, confirmed with Ctrl+Shift+Enter to turn it into an array formula:

=INDEX(C2:C13,MAX((A2:A13=E2)*(B2:B13=F2)*(C2:C13<>0)*(ROW(A2:A13)-ROW(A1))))

 

[#3 · Accepted solution]

@matt0020190 

Since I no longer use traditional spreadsheets, the Excel 365

=XLOOKUP(1, (Week=4)*(Period=1)*ISNUMBER(Value), Value,,,-1)

would be my preference, but

=LOOKUP(2, 1/(Week=4)/(Period=1)/ISNUMBER(Value),Value)

should still work.

[#4]

Super thanks, this was what I was already using minus the isnumber check! thanks again

[#5]

brilliant works well, just will prefer to use lookup as already got that in place. thanks for your solution