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.

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.

- Build vertical summary from Columns with Count
- Excel - Create a Unit Conversion sheet - Multiple links
- update cell when column header matches a list
- Delete text in cell subject to Worksheet_Change
- Counting the number of visible rows after autofilter
- Excel 2007: AVERAGEIF, SUMIF, COUNTIF, MAXIF, MINIF across multiple sheets, multple rows
- Import CSV data from a txt file skipping the first line and adding headers
- Simplifying SumIFs formulas for efficient excel formula
- I'd like to find out how to find the current streak of non-negative numbers in a row of data in Excel
- ValueError: Invalid character found in sheet title
- decrypt excel files
- Excel table search funcion that looks for partial string match
- Combine macros to filter on today's and tomorrow's date
- Converting line breaks to commas in excel sheet using Powershell
- Combinations of numbers arranged side by side as many as a random number (VBA)
- How can I remove ONLY leading and trailing spaces while leaving spaces in between words alone with an excel formula?
- VBA Date as integer
- Sliding Window Auto Increment Range
- Count cells with different conditional ranges
- Turning flattened pivots data into tables -Error tables can't overlap - Excel Vba
- How do I get only a specific part of a cell in Excel or Numbers?
- Calculate the Legendre symbol of two integers in Excel
- Unable to get text wrapping or vertical centering to work with xlsxwriter
- Number stored as text warning in excel using POI
- VBA Excel paste to the columns in regular intervals
- Convert filtered values from formulas to values
- Sorting Dictionary by nested class objects data [VBA]
- Events does not appear when making an Outlook query from Excel
- The script works well when I use MSXML2.XMLHTTP.6.0, but it fails miserably when I switch to MSXML2.serverXMLHTTP.6.0
- How to convert text string (d h m s) to time format in excel