Vlookup + Filter + Sort + Arrayformula workaround in google sheets

  Kiến thức lập trình

I have two tables, RECORDS and TYPES.

Link to a similar spreadsheet

RECORDS has 3 columns, “Type” in column A, “Entry Date” in column B, and “Status on Entry Date” in column C. “Type” and “Entry Date” are inputs from a Google Forms, “Status on Entry Date” needs to be an automatically calculated value.

Type (A) Entry date (B) Status on entry date (C)
a 05/02/2020 Active
a 05/02/2021 Inactive
a 05/02/2022 Inactive
a 05/02/2021 Inactive
a 05/02/2020 Inactive
b 05/02/2021 Active
b 05/02/2022 Inactive
b 05/02/2023 Active
b 05/02/2020 Active
b 05/02/2021 Active
c 05/02/2022 Active

TYPES has 3 columns, “Type” in Column E, “Status” in column F, and “Change of status date” in column G.

Type (E) Status (F) Change of status date (G)
a Active 01/01/2020
b Active 01/01/2020
c Active 01/01/2020
a Inactive 01/01/2021
b Inactive 01/01/2022
b Active 01/01/2023

In order to find the value of the column “Status on Entry Date” (C), a search needs to be done in TYPES table, finding the last status added to that type, before the “Entry date” (B).

E.G. The first record in RECORDS table is a type “a” and with entry date 05/02/2020. To find the value in “Status on entry date”, “a” has to be searched in TYPES table. There are 2 rows with Types “a” in TYPES table: the first has 01/01/2020 in date and “Active” as Status, the second has 01/01/2021 in date and “Inactive” as status. Since the second change was made after the first entry (, the status on “Status on entry date” will return “Active” value.

The way I did it at first was combining Vlookup, Sort and Filter. First I filter the TYPES table to only return records equal to or less than “Entry date” (B). then I sorted the table by Change of status (G) in descending order, so the most recent record is first, and then I searched for the entry “Type” (A).

If the first record is on A1, this will be the formula on C1:


Now I want to expand this formula for all rows in column C, to avoid copying and pasting every time a record is added.

Doing my research, I found that arrayformula does not work with filter. If it works for something, this is what it would have looked like if it worked:


As you can see, I have referenced all columns as an open range, and not only the range that actually has values in them. This is because records will be added in RECORDS table constantly, and new types and changes of status can also be added in TYPES table.

Any workaround will be appreciated. Thank you for your time!

Theme wordpress giá rẻ Theme wordpress giá rẻ Thiết kế website