Search code examples
excelexcel-formula

How do I assign a result depending on date using dynamic formula in excel?

I have two tables: The first is a table of roles where each person is in a team but person c has changed teams in June:

Name Team Start Date End Date
a x 01-Jan 01-Dec
b y 01-Jan 01-Dec
c x 01-Jan 01-Jun
c y 01-Jun 01-Dec

The second is a table of signings where it has the name of who signed it and when it was signed. What I need is a dynamic array formula that will show the team name of that person at the time of signing so signing 5 would be when person c was in team x, but signing 6 was when c was in team y. Person d doesn't exist in the role table so would need to show "not found"

Signing Name Date Of Signing Team Expected
1 a 01-Mar x
2 a 01-Apr x
3 b 01-Jan y
4 b 01-Jun y
5 c 01-Feb x
6 c 01-Jun y
7 d 01-Jul Not Found
Solution

  • Try using XLOOKUP() function, btw to spill can use either BYROW() or MAP():

    enter image description here

    =XLOOKUP(1,(B8=$A$2:$A$5)*(C8>=$C$2:$C$5)*(C8<$D$2:$D$5),$B$2:$B$5,"Not Found")

    Using MAP():

    =MAP(B8:B14,C8:C14,LAMBDA(α,δ,
 XLOOKUP(1,(α=A2:A5)*(δ>=C2:C5)*(δ<D2:D5),B2:B5,"Not Found")))

    Using BYROW():

    =BYROW(B8:C14,LAMBDA(α,
 XLOOKUP(1,(TAKE(α,,1)=A2:A5)*(TAKE(α,,-1)>=C2:C5)*
 (TAKE(α,,-1)<D2:D5),B2:B5,"Not Found")))

    And Spill output without using LAMBDA() helper functions:

    =LET(a, MMULT((C8:C14>=TOROW(C2:C5))*
              (C8:C14<TOROW(D2:D5))*
              (B8:B14=TOROW(A2:A5)),
        SEQUENCE(ROWS(A2:D5))),
     IFNA(INDEX(B2:B5,IF(a=0,NA(),a)),"Not Found"))