How to use FILTER function to get the previous row data based on certain fixed conditions in google sheet?

user_15002748 0
Viewed 493

enter image description here

Hi everyone,

I'm using the Filter function to get the Category based on 2 conditions below:

  • The Time in column A is greater than/equal to I3
  • The Number in column E is smaller than/equal to the Number in I4

Based on the 2 conditions above, it will return category I as the answer. However, I hope to get the Category before I which is Category H. Is there any ways to modified the Filter function so that the answer return will always be the previous category without changing the 2 conditions? First thing came out in my mind is something like =FILTER(...)-1, but it doesn't work that way.

Any help will be greatly appreciated!

google-sheets filter time function
2 Answers

try:

=INDIRECT(ADDRESS(MATCH(FILTER(B3:B19, E3:E19<=I4, A3:A19>=I3), B:B, 0)-1, 2))
user_56326290

Notice that FILTER can get any row/column of boolean as conditions. You can simply offset your them to take the conditions of the row at the bottom of them:

=FILTER(B3:B18, E4:E19<=I4, A4:A19<=I3)

Notice that doing this makes it so the last row can never be selected.

References

user_147798040
Related