Dynamically refer to a table in a worksheet index/match function

Hi all,

I want to be able to reference a table dynamically for an INDEX/MATCH function, and I want to base that reference on a cell value. For example, let's say I have four tables on a worksheet. Each have three columns, named "Column1", "Column2", and "Column3". The tables are named the following names:

Table2016
Table2017
Table2018
Table2019

And let's say the function looks like this if I reference the table range directly as an array:

=INDEX(Table2016, MATCH("Test", Table2016[Column1], 0), 3)

What I want to be able to do is put a cell reference in the array reference for the INDEX portion and the same cell reference in the lookup array reference for the MATCH portion. So, just as a rough example, I would make the value of cell A1 equal "Table 2016" and then write something like this:


=INDEX("=A1", MATCH("Test", "=A1"[Column1], 0), 3)

I know this example does not work.

Before anyone gives a possible answer, let me state the following: No, I cannot use macros for this because I have been asked not to use macros for the spreadsheet. No, I can't use a VLOOKUP instead because there will be times when I have to look at the last column and return a value from the first. I need to use worksheet functions only, and I need the function to be able to dynamically reference a table name if it's possible.

Thanks!

That seems to work fine, but I'm concerned about using INDIRECT. I know it can really slow down computation, and there are going to be over a thousand of these references in the worksheet.

Yeah, I love macros and could easily do this with a macro, but I have been specifically told not to use macros for this particular spreadsheet. I will try the INDIRECT and see how it cooperates.