excelexcel-formulaexcel-2010

Dynamically change the Lookup range/s


I am trying to change the below formula to automatically change the lookup table based on the value in column work week

I have 3 identical tables (Week1, Week2, Week3)

=INDEX(Week1[[Mon]:[Sun]],MATCH(CLS[@CLS],Week1[Agent],0),MATCH(CLS[@Day],Week1[[#Headers],[Mon]:[Sun]],0))

I want to change the look-up table based on what week work week is in the work week column.

I have attempted to change this myself but I keep getting #VALUE!. My attempt is below.

=INDEX(CLS[@[Work Week]]&"[[Mon]:[Sun]]",MATCH(CLS[@CLS],CLS[@[Work Week]]&"[Agent]",0),MATCH(CLS[@Day],CLS[@[Work Week]]&"[[#Headers],[Mon]:[Sun]]",0)).

My other thought was to use Indirect but I couldn't get my head around it.

enter image description here

enter image description here


Solution

  • I don't think CHOOSE is volatile, so should be better than using INDIRECT.

    I've removed date formatting to show the values used in the table headers, and used sequential numbers rather than hours to make it easier to see what figure is being returned.

    =INDEX(
        CHOOSE(SUBSTITUTE([@[Work Week]], "Week", ""), Week1[#All], Week2[#All], Week3[#All]),
        MATCH(
            [@CLS],
            INDEX(CHOOSE(SUBSTITUTE([@[Work Week]], "Week", ""), Week1[#All], Week2[#All], Week3[#All]), 0, 1),
            0
        ),
        MATCH(
            TEXT([@Day], "dd-mmm"),
            INDEX(CHOOSE(SUBSTITUTE([@[Work Week]], "Week", ""), Week1[#All], Week2[#All], Week3[#All]), 1, 0),
            0
        )
    )  
    

    Table: With Week removed from the Work Week column I can use CHOOSE to select the correct table.
    Row: I then use INDEX(<Table>,1,0) to return the first column and MATCH to find the location of the Agent.
    Column: Next I use INDEX(<Table>,0,1) to return the first row and MATCH to find the date - the headers in the table are treated as text, so the I use TEXT to convert the date to text in the correct format.
    Finally I use INDEX(<Table>, <Row>, <Column> to return the correct cell.

    enter image description here

    enter image description here