CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaspreadsheet
Maksym Katsovets
Maksym Katsovets

Reputation: 147

Google spreadsheet formula with conditions for array

I would like to find the right formula to see how the category of accounts changed over time. There are 2 tables: List - contains account id, start date, end date, current category, and date columns Status Change - contains account id, transfer date, changed from, changed to. I need to find the right formula for date columns in the List tab. It should work in this way:

  1. If there is only a start date for the account and there are no records for this account in the Status Change tab, the current status should be the same for all periods till today.
  2. If there is an end date for the account, the status should be empty after his date.
  3. If there are records for an account in the Status Change tab, the status should be reflected accordingly in the List tab.

Here is the example table - https://docs.google.com/spreadsheets/d/1yw3t7oiuRUcWkzCuEB-20sMcnwOnt3BTmS7jjEdphIQ/edit#gid=1693652082

List tab contains the result that should be returned by formula.

Upvotes: 0

Views: 85

Answers (1)

Nami888
Nami888

Reputation: 247

Hey to achieve your intended goal from "List" tab, it will be an extremely complicated set of if else conditions:

=if($D2="",if(E$1<$C2,"", ifna(if(index('Status Change'!$G$2:$G,match($A2,'Status Change'!$F$2:$F,0))="","Status 1", if(E$1<index('Status Change'!$G$2:$G,match($A2,'Status Change'!$F$2:$F,0)),"Status 1",if(index('Status Change'!$H$2:$H,match($A2,'Status Change'!$F$2:$F,0))="","Status 2",if(E$1<index('Status Change'!$H$2:$H,match($A2,'Status Change'!$F$2:$F,0)),"Status 2","Status 3")))),"Status 1")),if(E$1<$D2,if(E$1<$C2,"", ifna(if(index('Status Change'!$G$2:$G,match($A2,'Status Change'!$F$2:$F,0))="","Status 1", if(E$1<index('Status Change'!$G$2:$G,match($A2,'Status Change'!$F$2:$F,0)),"Status 1",if(index('Status Change'!$H$2:$H,match($A2,'Status Change'!$F$2:$F,0))="","Status 2",if(E$1<index('Status Change'!$H$2:$H,match($A2,'Status Change'!$F$2:$F,0)),"Status 2","Status 3")))),"Status 1")),""))

I have appended the formula into the "List Test" tab. Try to read up on what I have wrote and let me know if you have trouble understanding it. Thx

Upvotes: 1

Related Questions