Reference a cell relative to another cell Google Sheets

user_3512586 0
Viewed 68

I am working with a dataset like this:

Sheet1
Term    Multiplier
Term1   M1
Term2   M2
Term3   M3
Term4   M4
Term5   M5

Sheet2
TermA       TermB       TermC       Score
=Sheet1!A2  =Sheet1!A3  =Sheet1!A5  (M2+M3+M5)
=Sheet1!A3                          (M3)
=Sheet1!A1  =Sheet1!A2              (M1+M2)
=Sheet1!A2  =Sheet1!A4              (M2+M4)

Sheet1 has a static list of Terms and its Multipliers. Sheet2's list items can have up to 3 references to a Term in Sheet1.

What I am trying to do is calculate a score in Sheet2. It should find the Terms that are in the row, look up their multipliers, and add those up. However, I am struggling to figure out how I can reference the multipliers. Basically, I need to go through the references at TermA, B and C and look at the value next to the target of that reference. I have found the functions FORMULATEXT, INDIRECT and ADDRESS, which offer part of the functionality that I need, but I can't figure out how to combine them (if this is even the way).

google-sheets-formula google-sheets
2 Answers

This could be simpler if INDIRECT works with ARRAYFORMULA, but it doesn't, so one thing I thought is to manually calculate the pointer using INDIRECT per cell then add them after appending Sheeet1! to locate the values of those pointers in Sheet1.

Formula:

=IFERROR(INDIRECT(CONCAT("Sheet1!", VLOOKUP(A2, Sheet1!A:B, 2)))) +
IFERROR(INDIRECT(CONCAT("Sheet1!", VLOOKUP(B2, Sheet1!A:B, 2)))) +
IFERROR(INDIRECT(CONCAT("Sheet1!", VLOOKUP(C2, Sheet1!A:B, 2))))

Data:

data

Output:

output

user_161186860

I would use for instance:

=IFERROR(VLOOKUP(A2,Sheet1!$A$2:$B$6,2,false),0) + IFERROR(VLOOKUP(B2,Sheet1!$A$2:$B$6,2,false),0) + IFERROR(VLOOKUP(C2,Sheet1!$A$2:$B$6,2,false),0)

user_125328670
Related