I am trying to find a way in Google Sheets to return the last cell based on certain criteria. I've included the table below:
| Jan | Feb | March | April | LATEST SALE | |
|---|---|---|---|---|---|
| group 1 | sale | sale | no sale | sale | April |
| group 2 | no sale | no sale | sale | no sale | March |
I am trying to have the "LATEST SALE" Column return the values I listed above: "April, "March", but I am having trouble finding the right formula.
I know there is a lookup and sort formula, something like =LOOKUP(H2,SORT(B2:D2),SORT(B1:D1,B2:D2,TRUE))
Perhaps there is something for a horizontal lookup and sort? Thanks for any help, it is much appreciated!
CodePudding user response：
try:
=IFNA(BYROW(B2:E, LAMBDA(x, LOOKUP(1, INDEX(1/(x="sale")), B1:E1))))
if you want to drag it use:
=IFNA(LOOKUP(1, INDEX(1/(B2:E2="sale")), B1:E1))