Forum Discussion

dangreen755's avatar
dangreen755
Copper Contributor
Sep 24, 2024

Find value in matrix based on today's date in column

I have a matrix with a date sequence 

=SEQUENCE(1;366;(DATE($D$6;1;1));1)

in the columns and names (text) in the rows.

 

I would like to write a formula that matches three search criteria: (1) today's date (column D in the picture), AND (2) the value 'Duty' in the matrix, AND (3) the value 'Manager' in column A. The return value in the example should be "Carl" (B7).

 

excel
Formulas and Functions
  • Harun24HR's avatar
    Harun24HR
    Sep 24, 2024

    dangreen755 You can use FILTER() function.

     

    =FILTER(B3:B8,(A3:A8="Manager")*((FILTER(C3:F8,C2:F2=TODAY())="Duty")))

     

     XLOOKUP() should also work.

    =XLOOKUP(1,(A3:A8="Manager")*(XLOOKUP(TODAY(),C2:F2,C3:F8)="Duty"),B3:B8,"")

    See the attached file.

     

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Sep 24, 2024

    dangreen755 You can use FILTER() function.

     

    =FILTER(B3:B8,(A3:A8="Manager")*((FILTER(C3:F8,C2:F2=TODAY())="Duty")))

     

     XLOOKUP() should also work.

    =XLOOKUP(1,(A3:A8="Manager")*(XLOOKUP(TODAY(),C2:F2,C3:F8)="Duty"),B3:B8,"")

    See the attached file.

     

Resources