Forum Discussion

BNB2022's avatar
BNB2022
Copper Contributor
Dec 17, 2022

How to reference data by a name and a date

Hello,           My organization sends out a ton of reporting to track various things, but none of it is centralized. I have a spreadsheet that collects a lot of this reporting and centralizes it. A...
excel
Formulas and Functions
Lorenzo's avatar
Lorenzo
Silver Contributor
Dec 17, 2022

Hi BNB2022 

 

With 365:

in I4:

=LET(
  SourceCols, SEQUENCE(,COLUMNS(Source)),
  Names,      SCAN("",Source[Name],
    LAMBDA(seed,name, IF(ISBLANK(name),seed,name))
  ),
  TeamNames,  SORT(TeamList[Name]),
  Values,     REDUCE(0,TeamNames,
    LAMBDA(seed,name,
      VSTACK(seed,FILTER(CHOOSECOLS(Source, DROP(SourceCols,,2)), (Names=name)*(Source[Date]=TargetDate),0))
    )
  ),
  IFNA(VSTACK(FILTER(Source[#Headers], SourceCols<>2), HSTACK(TeamNames,DROP(Values,1))),0)
)

 

 

Resources