求适配Google Sheets的多条件数组公式：实现账户类别随时间变化的动态映射

I've worked out an array formula that handles all your requirements for showing account categories across dates in the List sheet, pulling in data from both the List and Status Change tables. Here's how to implement it:

First, let's recap your requirements to make sure we're aligned: #

• For accounts with only a start date and no entries in Status Change: Keep the current category from the start date through today.
• For accounts with an end date: Show empty values for any date after the end date.
• For accounts with status changes: Accurately reflect the category that was active on each target date based on the transfer records.

The Array Formula #

Assuming your List sheet has:

• Column A: account id
• Column B: start date
• Column C: end date
• Column D: current category
• Row 1 (columns E onwards): Your target dates (e.g., monthly dates)

Paste this formula into cell E2 (it will auto-expand to cover all rows and date columns):

=ARRAYFORMULA(
  IF(
    ISBLANK(A2:A),,
    LET(
      account_ids, A2:A,
      start_dates, B2:B,
      end_dates, C2:C,
      current_categories, D2:D,
      target_dates, E1:1,
      BYROW(account_ids, LAMBDA(acc,
        LET(
          acc_start, XLOOKUP(acc, account_ids, start_dates),
          acc_end, XLOOKUP(acc, account_ids, end_dates),
          acc_current, XLOOKUP(acc, account_ids, current_categories),
          BYCOL(target_dates, LAMBDA(date,
            IF(
              // Skip dates before start or after end
              (NOT(ISBLANK(acc_end)) && date > acc_end) || date < acc_start,
              "",
              // Find the latest status change on or before the target date
              LET(
                latest_change_date, MAXIFS('Status Change'!B:B, 'Status Change'!A:A=acc, 'Status Change'!B:B<=date),
                IF(
                  ISBLANK(latest_change_date),
                  // No changes? Use the current category
                  acc_current,
                  // Pull the category after the latest change
                  XLOOKUP(latest_change_date, 'Status Change'!B:B, 'Status Change'!D:D, "", 0, 1)
                )
              )
            )
          ))
        ))
      )
    )
  )
)

How This Formula Works #

Let's break down the key parts:

1. ARRAYFORMULA + LET: ARRAYFORMULA ensures the formula applies to all rows/columns automatically, while LET defines variables to make the logic easier to follow and maintain.
2. BYROW + BYCOL: These functions iterate over each account row and each target date column, calculating the correct category for every intersection.
3. Date Validation: First, we check if the target date is outside the account's active period (before start date or after end date) — if so, we return an empty value.
4. Status Change Lookup: Using MAXIFS, we find the most recent status change that happened on or before the target date. If no such change exists, we fall back to the account's current category. If there is a change, XLOOKUP fetches the category the account was changed to.

Important Notes #

• Make sure all date columns (start date, end date, transfer date, and target dates) are formatted as Date in Google Sheets — this ensures the formula compares dates correctly.
• If an account has multiple status changes, the formula always uses the latest one that occurs on or before the target date.
• For accounts without an end date, the formula will show the current category (or latest changed category) for all dates from the start date up to your target dates (including today, if included in your date columns).

内容的提问来源于stack exchange，提问作者Maksym Katsovets