I'm trying to automate a table that ranks criteria for a design process. The table itself contains standardized descriptions of the "wants" for the design, and then 'weights' the wants by perceived importance. I've attached the workbook to hopefully clarify things a bit.
The weighting system works by giving each cell in the table a unique multiplier. Then, by adding up the numerical values from each instance of the "want" in the table, you get a final score for it. I'd like to have Excel do this automatically so if one of the values changes, the entire table doesn't have to be recalculated by hand.
In English, the operation would be something like:
1) Search table/array for <want1> and find all instances
2) For each instance found, calculate: Multiplier*(Weightfactor/Rank)
3) Sum the calculations from all instances and report in output "score" cell
4) Do this for all listed "wants"
I've been fooling around with Index, Vlookup, Match, ect. but I just can't find a way to do it with multiple columns to search through. I'm wondering if this can only be done with vba?