Excel lookups function to find a structured table and column

Question

I have a worksheet with a bunch of tables. The tables are structured, however the Titles (TABLE1, TABLE2) are not part of the actual table. I am trying to make a function that finds which table corresponds to the Title and then looks up at COLUMN3 to return the value SEVEN. Here's what they look like. I am trying to make this function flexible enough so that if I change the value to TABLE2, it will return GG.

TABLE1 +---------+---------+---------+----------+ | column1 | column2 | COLUMN3 | column4 | +---------+---------+---------+----------+ | row1 | xxx | xxx | xxx | | row2 | xxx | SEVEN | xxx | | row3 | xxx | xxx | xxx | | row4 | xxx | xxx | xxx | +---------+---------+---------+----------+

TABLE2 +---------+---------+---------+----------+ | column1 | column2 | COLUMN3 | column4 | +---------+---------+---------+----------+ | row1 | bb | cc | dd | | row2 | ff | GG | hh | | row3 | zzz | zzz | zzz | | row4 | zzz | zzz | zzz | +---------+---------+---------+----------+

I'm not sure where to start. My VBA knowledge is nonexistent, so I'm trying to just figure this out with just one super nested function (though any hints/solutions are welcome). Any ideas? Thanks in advance!

Answer 1

You can use the table name (if you use table objects) or range names with an Indirect function. Consider the screenshot below.

The tables are called TableBlue and TableYellow and have been created with Insert > Table. Structured references can be used to refer to elements of the table, for example TableBlue references all table rows in the blue table, TableYellow[#Headers] points to the header row of the yellow table.

This text can be plugged into an Indirect() function, which will then point to the respective range. The formula in E11 puts its components together with Indirect and uses the text in the cells to the left. See how when it is copied down, it looks in a different table, and can even return a value from a different column.

=VLOOKUP(C11,INDIRECT(B11),MATCH(D11,INDIRECT(B11&"[#Headers]"),0),FALSE)

This kind of Indirect() construct can be used in other formulas, not just a Vlookup, of course. It depends what you want to achieve. Be aware, though, that Indirect is volatile and can slow things down, especially in a large workbook.