Forum Discussion
Get latest value from table based on multiple criteria
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
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 Replies
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.
- Super thanks, this was what I was already using minus the isnumber check! thanks again
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))))
- brilliant works well, just will prefer to use lookup as already got that in place. thanks for your solution
