CODEWITHSUNDEEP
functiongoogle-sheetstimefilter
weizer
weizer

Reputation: 1127

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

enter image description here

Hi everyone,

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

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!

Upvotes: 3

Views: 1539

Answers (2)

player0
player0

Reputation: 1

try:

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

Upvotes: 1

Mart&#237;
Mart&#237;

Reputation: 2871

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

Upvotes: 0

Related Questions