3

Here is a simplified example of what I want:

sheet 1
enter image description here

sheet 2
enter image description here

Essentially Column C finds the row on Sheet 2 with the same Item as Sheet 1 (Columns A) and sums up the Upgrade cost for the current Upgrade level.

So an Upgrade level of 3 finds the sum of Sheet 2, B:D where the Items are the same, where an Upgrade level of 5 will find the sum of B:F.

1

Add the following line of code into Sheet1!C2:

=SUM(OFFSET(INDIRECT("Sheet2!B"&MATCH(A2;Sheet2!A:A,0)),0,0,1,B2))

The formula explained from the inside to the outside:

  1. MATCH in an equivalent of the VLOOKUP. It will return a relative position as an integer, rather than a specific value.
  2. INDIRECT combines a STRING with a value into a reference (B2).
  3. OFFSET is a not commonly used formula, but quite powerful. It sets the range of the SUM.
  4. SUM will sum the outcome.

See the example file I created: Finding the SUM of an Array

  • Thank you Jacob, this seems to work. However, it inexplicably does not work sometimes? I have expanded my example spreadsheet to illustrate the problem: docs.google.com/spreadsheet/… – Rushmik Apr 1 '13 at 3:45
  • @Rushmik I found out what was causing the mayhem. In the MATCH formula, I've added ,0. This solves the problem. Sorry for that... – Jacob Jan Tuinstra Apr 1 '13 at 8:49
  • No problem at all! Everything works perfectly now - thanks. – Rushmik Apr 1 '13 at 11:21
-1

For the first part of you problem you can use VLOOKUP().

You can use VLOOKUP to search the second tab range for the row with the rocket launcher in it. Then you can specify which number you want to get returned by stating the index (index 1 is first column in the range).

The problem of you current Sheet2 is that the numbers aren't cumulative. If you make it cumulative, so values 10, 40, 80, 180, 680, you can use the (upgrade number)+1 as your index.

So in the upgrade cost cell C2 you would get something like:

=VLOOKUP(A2;Sheet2!A:F;A3+1;False)
  • This is very helpful, yet I need to keep the Sheet 2 values as they are (not cumulative) so they can be viewed and edited more easily. Is there a way to turn two VLOOKUP()s into a range to put within a SUM()? Such as SUM(VLOOKUP()&":"&VLOOKUP())? – Rushmik Mar 31 '13 at 4:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.