Cell Reference Challenge

[absherzad]

Hello everyone,

I have two tables named Movies and Users.
I want to create let's say something like a matrix of Movies and Users table by adding and multiplying a row from Users with another row from Movies. The SUMPRODUCT is an elegant method doing the calculation.

The problem is I have to manually write a formula for each user and then drag it for all movies, and I have to repeat this process for another user and all movies. The problem is with RELATIVE and ABSOLUTE cell reference!!!

I just want to know if it is possible to do without VBA in Excel in efficient way. Please see the attached screenshot to have a better understanding of the subject.

Regards,

 

[pgc01]

Hi

Not tested (because I don't have access to the data, you just posted a picture), but if I understood what you want, try

in C17:

=SUMPRODUCT(INDEX($H:$J,MATCH(C$16,$G:$G,0),0),INDEX($C:$E,MATCH($B17,$B:$B,0),0))

Copy down and across

 

[absherzad]

Thanks a lot
A nice trick. I am wondering why I did not think of considering INDEX and MATCH.

The only concern is that the row and column headers must be identical corresponding to their actual headers which is not a big deal.

Regards,

 

[pgc01]

You're welcome.