How to dynamically change VLOOKUP cell range?

[dmagnus]

I have an Excel 2010 pivot table where WEEK and TITLE are the Row Labels. For each of 5 WEEKs there are X number of TITLES. As new weeks are rotated in and out (rolling time frame) the cell ranges for each WEEK’s data change. I’m looking for a way to identify each row of the Pivot Table that represents the WEEK subtotal.

I’m trying to do this because I have VLOOKUPs that reference each WEEK’s data. Currently I have to manually change the formula in each VLOOKUP to each WEEK’s cell range as old WEEKs are de-selected and new weeks are selected. I’d like to make my VLOOKUP formula dynamic, so as new WEEKS are selected the cell ranges are automatically adjusted.

Here is one of the VLOOKUP formulas I’m using:
=IFERROR(VLOOKUP($B342,$B$115:$AA$212,MATCH(C$6,$C$6:$AA$6,0)+1,FALSE),0) * VLOOKUP(C$6,Conversion!$A$3:$B$29,2,FALSE)

It is the first VLOOKUP cell range I am trying to make dynamic. One week the range can be B115-AA212, the next it can be B119:AA26. One possible solution I came up with is to use the MATCH function to identify each WEEK’s subtotal row, though I don’t know how to fit the MATCH function into the VLOOKUP formula.

Do you have any ideas? Thanks in advance for any thoughts/suggestions you may have.

 

[sthrncaliguy]

It is hard to say the best resolution without seeing your data table, and what criteria would determine the range for your VLOOKUP. IE should the VLOOKUP automatically adjust based on the current date ie =TODAY(), or would there be a dropdown or other option for the user to select a date range, and the VLOOKUP would update accordingly?


I have used a combination of INDIRECT and CONCATENATE formulas in the past to achieve dynamic vlookups. Here is an example that might help you get started.

=VLOOKUP(B3, INDIRECT(CONCATENATE(A2, "!", B2)), 2, FASLE)

In this example, the red portion is replacing the typical "Sheet1!A1:A15" portion of the fomula. Cell A2 houses the Sheet Name and B2 houses the cell range (i.e. "A2:B150"). Either of which could be updated dynamically.

Then all you need is a way to change the data in cells A2, B2 respectively to effectively have your VLOOKUPS pointing to the correct location for data. That could be any number of input controls, or even another formula in itself.

If you want to post a sample of your data table, and specifics on what factors determine what range your VLOOKUP should point to, and I could probably help customize the above, or even a more appropriate solution, depending on your needs.

-Jeremy

 

[Atroxell]

Hey sthrncaliguy--this is a pretty cool approach to the solution.

I have never tried anything like your vlookup and concatenate combo, but I can assure you that your suggestion will fit into my work sooner or later. I am always looking for new ways to perform the same processes. Thanks for the new insight!

(Even if this wasn't my thread--so now I will butt out! )