0

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!

Thomas Price
  • 81
  • 7

1 Answers1

0

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.

enter image description here

teylyn
  • 34,374
  • 4
  • 53
  • 73
  • Thanks for your answer! Yes, I am using table objects and I think Indirect() is definitely something I will look into to fit in with my Index Matches. I have a question about your example: What would you recommend doing if the table names are not 1-to-1 with my specified Table names? e.g. my title is _"Table Blue Y"_ and my table name is _TableBlue_ – Thomas Price Jun 28 '17 at 03:59
  • What title? Where does a title come in? – teylyn Jun 28 '17 at 08:56
  • By 'title' I mean the cell that says `TableBlue` and `TableYellow` – Thomas Price Jun 28 '17 at 14:37
  • That is just decoration. It is not used in the formula. The name of the table (what you see in the name box of the Table Tools ribbon), is what counts. – teylyn Jun 28 '17 at 22:50