Search code examples
excelexcel-formulaexcel-365

Comparing two dynamic ranges

I have the following summation table

Range 1 (expected output)

Responsibility Center Authorized HC # of Vacancies Pending Approvals
County 1 26 1 2
County 2 7 21 1
County 3 53 35 1
County 4 53 35 0

Range 2

Create Date Incumbent Office Title Responsibility Center
5/23/2024 John Jones Housing Assistant County 1
5/23/2024 Jane Joe Housing Assistant County 2
5/28/2024 Jim Jong Housing Assistant County 3
5/28/2024 Cang Cong Housing Assistant County 1

Basically, the pending approvals in range 1 need to be drawn from the responsibility center column in range 2. So, there are values for county 1 2 and 3, but not 4, so I don't expect any value to show up in that cell. The list in range two can be dynamically sized, so I'd prefer to not hardcode the range in if I can.

I tried the following two functions in column d, copy pasted down in d2:d5

= countif(d18#, $A2)
=MAP(A5#, CHOOSECOLS(A18#, 4), LAMBDA(a,b, COUNTIF(a, b)))

both didn't work!

What I am trying to do is the formula

= countif(d18$D21, a2)

but to have the range dynamically sized

Solution

  • You could try using the following formula:

    enter image description here

    • Formula used in cell D2

    =MAP(INDEX(A2#,,1),LAMBDA(α, SUM(COUNTIFS(α, INDEX(F2#,,4)))))

    Another alternative using MMULT()

    enter image description here

    =MMULT(N(INDEX(A2#,,1)=TOROW(INDEX(F2#,,4))),SEQUENCE(ROWS(A2#))^0)

    Using BYROW() without LAMBDA() construct with CHOOSECOLS()

    enter image description here

    =BYROW(--(CHOOSECOLS(A2#,1)=TOROW(CHOOSECOLS(F2#,4))),SUM)