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:
=VLOOKUP(A1;SORT(FILTER(E$:G$;G$:G$<=B1);3;FALSE);2;FALSE)
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:
=ARRAYFORMULA(VLOOKUP(A:A;SORT(filter(E$:G$;G$:G$<=A:A);3;FALSE);2;FALSE))
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!