Array multiplication

Do you have 365 or 2021? It matters, as there are significant differences.

I would use FILTER to isolate the correct column from the matrix and then multiply.

=FILTER(matrix,header=x)*your_range

I’m on my iPhone so can’t give an exact formula at the moment.

Please change your profile to the OLDEST version you need solutions to work for ONLY.

The suggestion in post #2 should work in either.

For O365, delete ALL expected results:

=LET(A,F5:F8,B,B3:E3,C,Sheet1!E4:H7,D,Sheet1!E2:H2,A*DROP(REDUCE("",B,LAMBDA(y,z,HSTACK(y,FILTER(C,D=z)))),,1))

for both:

=$F$5:$F$8*FILTER(Sheet1!$E$4:$H$7,Sheet1!$E$2:$H$2=B3)

copied across.

Please try:

Formula:

Please Login or Register  to view this content.

Thanx Ali,Glenn & Hans.All worked.

Can you kindly explain me if the source data is not in proper order or not arranged properly then whether it would give me the correct result by above formula.

What I have seen that A5:A8 of Sheet2 was not found in the formula with that of matching of Sheet1 of D4:D7 & vice versa.

Can you kindly explain me if the source data is not in proper order or not arranged properly then whether it would give me the correct result by above formula.
What happened when you tried them in a different order?

What I have seen that A5:A8 of Sheet2 was not found in the formula with that of matching of Sheet1 of D4:D7 & vice versa.
Please post a file showing what you mean.

Pls see that I have changed the order both row & column header in Sheet2 with that of red font.

What would be the result ? same or different ?

I think I have tested it gives me incorrect result.Above formula is applicable if both row & column header are same.If both are different then it won't works.

In B5 copied across and down:

=LET(f,FILTER(Sheet1!$E$4:$H$7,Sheet1!$E$2:$H$2=B$3),FILTER(f,Sheet1!$D$4:$D$7=$A5))*$F5

Old-fashioned

Formula:

Please Login or Register  to view this content.

!

Or try in B5 and copy to the right and down:

Formula:

Please Login or Register  to view this content.

1. Delete all expected results.

2. For 2021, in B5, copied ACROSS:

=$F$5:$F$8*INDEX(FILTER(Sheet1!$E$4:$H$7,Sheet1!$E$2:$H$2=B3),MATCH($A$5:$A$8,Sheet1!$D$4:$D$7,0))

3. Or for O365, in B5, no copy paste at all:

=DROP(REDUCE("",B3:E3,LAMBDA(y,z,HSTACK(y,F5:F8*INDEX(FILTER(Sheet1!E4:H7,Sheet1!E2:H2=z),MATCH(A5:A8,Sheet1!D4:D7,0))))),,1)

Thanks for the feedback. Glad to have helped. .

If you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.