title: How to populate a VLOOKUP with a dynamic range in Google Sheets?
body: <p>I have a file that needs to look up the team name (on a tab called "Parse") of an employee on a tab called "Roster".  This can be done with a simple array Vlookup, however, the issue is that each week, we add a row to the Roster tab because employees sometimes move around.</p>
  
  <p>I have already done the majority of legwork by using a MAX/FILTER/COLUMN, LEN formula to determine what the last column is in the Roster tab.  I have also created a range that will provide me with the actual range needed for the VLookup.  For example:  If the last column in Roster is "G", I already have a formula that populates "'Roster'!A2:G", which is the range I would need for my Vlookup.  If the last row in Roster is "P", then the formula shows "'Roster'!A2:P".</p>
  
  <p>The formula that gives me the range, as I described in the examples, lives in cell F2 on the Parse tab.</p>
  
  <p>So the hard part is done.  All I need to know now is, when writing my Vlookup, how can I have the formula reference the actual contents of cell F2 in the Parse tab instead of thinking the lookup range is cell F2.  Here's a visual:</p>
  
  <p>Cell F2 in the Parse tab reads:  'Roster'!A2:G (because as of now, column G is the last column with data.  this is correct).</p>
  
  <p>My VLookup is <code>VLOOKUP($A2,$F$2,7,FALSE)</code>.  However, I want my Vlookup to say <code>VLOOKUP($A2,'Roster'!A2:G,7,FALSE)</code>.</p>
  
  <p>Is it possible to have the formula reference what is IN cell F2 instead of referencing just "F2"?</p>
  
  <p>I hope this made sense.  It's the end of my workday and I'm very tired.</p>