Forum Discussion
Solved
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...
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.
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