Using an INDEX MATCH function on filtered rows-CodePudding

I have the following two tables:

What I want to do is multiply the values in table 2 with the corresponding factors in table 1. A value in table 2 should be multiplied with the factor who's 'Letter' matches the 'Tag' in table 2. For example: Row 13 has a value of 3 and the tag is 'A'. 'A' in table 1 corresponds with a multiplication factor of 0.5, thus the multiplication that must take place is 0,5*3. This multiplication must be done for every row in table 2 and then the values must be summed and divided by the sum of the corresponding multiplication factors for every row in table 2. For example, if you only do this for rows in table 2 with tag 'A', then the computation must be: (3 * 0.5 5 * 0.5)/(0.5 0.5)=4.

Cell B19 shows the outcome for all rows. The formula I used was:

=SUMPRODUCT(B13:B17;INDEX(B3:B6;MATCH(A13:A17;A3:A6;0)))/SUM(INDEX(B3:B6;MATCH(A13:A17;A3:A6;0)))

The problem I have is that this doesn't work when I filter the rows in table 2. I can't get SUBTOTAL to work, eventhough I tried a lot. I also tried using an SUBTOTAL/OFFSET function, as I found this on the web, but this also did not work. That function looked like this:

=SUMPRODUCT(B3:B6;SUBTOTAL(9;OFFSET(B13:B17;ROW(B13:B17)-MIN(ROW(B13:B17));0;1)))

How do I make this work when I filter the rows in table 2?

CodePudding user response：

I switched to structured table references:

=LET(ξ,INDEX(Table1[Multiplication factor],MATCH(Table2[Tag],Table1[Letters],0)),ζ,SUBTOTAL(3,OFFSET(INDEX(Table2[Tag],1),SEQUENCE(ROWS(Table2[Tag]),,0),)),SUMPRODUCT(ζ,Table2[Values],ξ)/SUMPRODUCT(ζ,ξ))