title: Sum from lookup or whatever works
body: <p>I'm wanting to sum a column from a certain point to another certain point which is dictated by the user and a drop-down box. Awkward to explain, I think best to view <a href="https://docs.google.com/spreadsheets/d/1wI3DhjmqTWS69Vf6dx8RIF-jG4CIRJKhxL91spg4Oo0/edit?usp=sharing" rel="nofollow noreferrer">spreadsheet</a>.</p>
  
  <p>Something along the lines of this is my thought process:</p>
  
  <pre><code> SUM(   
     VLOOKUP(CURRENT LEVEL CELL,      
           VLOOKUP(DROP DOWN BOX VALUE,NAMED RANGE 'RARITY',COLUMN 2,FALSE) , COLUMN 2, FALSE)    
     :VLOOKUP(DESIREDLEVEL CELL,      
           VLOOKUP(DROP DOWN BOX VALUE,NAMED RANGE 'RARITY',COLUMN2,FALSE) , COLUMN 2, FALSE)     
       )
  </code></pre>
  
  <p>Which would be:  </p>
  
  <pre><code>=SUM(VLOOKUP(F11,VLOOKUP(F10,rarity,2,FALSE),2,FALSE):VLOOKUP(F12,VLOOKUP(F10,rarity,2,FALSE),2,FALSE))
  </code></pre>
  
  <p>However, that just gives me errors:</p>
  
  <blockquote>
    <p>VLOOKUP evaluates to an out-of-bounds range  </p>
  </blockquote>
  
  <p>so I can't even see if it would be summing all the values between those two points or just those two points themselves.</p>
  
  <p>Essentially I'm trying to get the first VLOOKUP's range, to be a VLOOKUP of the drop-down box. Assuming we can get that working somehow, I then want it to sum the values from the first VLOOKUP's end cell, to the VLOOKUP after the : found cell.</p>
  
  <p>Is there a way to have the result of a VLOOKUP set as the named range?</p>
  
  <p>Ie, <code>vlookup2</code> returns "rare", <code>vlookup1</code> uses <code>vlookup2</code>'s result as the named range;</p>
  
  <pre><code>=vlookup1(A1, vlookup2 result, column, false) 
  </code></pre>
  
  <p><strong>TL:DR</strong></p>
  
  <p>=vlookup(X, one of multiple named ranges dictated by what is in the drop down box, 2, false)</p>
  
  <p><a href="https://i.stack.imgur.com/aemX2.gif" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/aemX2.gif" alt="Example re SO Q51404833"></a></p>