Change Table_array Automatically in Excel Lookup Formulas

[Patrikmw]

Hi,

I am trying to improve a template that will be used by alot of people on a weekly basis, and my goal is to make it as automatic as possible. Some columns need to be updated every week by the users, currently by changing the cell reference (marked in red) in this formula:

=IFERROR(HLOOKUP(0;data_conversion!C9:DB9;1;FALSE);-1)

As you can imagine this is both time consuming and inefficient, especially since some people are not even used to excel formulas...

The cell reference is changed by one cell to the right every week, eg. C9:DB9 week 1, D9:DC9 week 2 and so on. Can this be done automatically by combining functions? Preferrably I would like to link the table_array to a cell that can be changed manually. If possible I would like to avoid using macros, but that may not be possible.

I really appreciate your help on this matter since I have not found a solution yet!

BR,
Patrik

 

[Kamolga]

Hi Patrik,

What habout having the week num on the line above and make a search on actual week num (=weeknum(today()).

If every week a colomn is added to the right (creating DC9 on week 2):

Otherwise, if you select data_conversion!C9:DB9 and go on insert table (go in design tab to take off blue lines and change its name if you want to), you will be able to make your search on =IFERROR(HLOOKUP(0;table1;1;FALSE);-1) (The name of the table will come automatically when selecting the array,but take off the colomns designations).
The good point with this approach is that the table will automatically extend every time you create a colomn to the right (or line below) and you do not have to adapt the table_array in your formula anymore normally.

 

[Patrikmw]

Hi Kamolga,

Thank you for your reply! In my case I would always like to scan for missing values (0) for two years back (104 weeks), which is specified by the table_array "data_conversion!C9:DB9", and not for a specific week only. As time passes I would like to scan the most recent 104 weeks, i.e. change the table array from data_conversion!C9:DB9 to the appropriate reference, ex data_conversion!D9:DC9. Thus, the table array doesnt grow each week, it just changes it's source (and hence method 2 is not applicable?)

Clarification: I work in Excel 2010.