Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

Here is a simplified example of what I want:
sheet 1
enter image description here

sheet 2
enter image description here

The yellow highlighted areas are areas important to the formula.

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.

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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

share|improve this answer
    
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
add comment

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)
share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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