Excel structured reference with variable name

Question

I have a table TABLE and two cells E1 and E2. The entry of E1 is guaranteed to be a header of TABLE. I'd like to write a formula to check wether the entry of E2 is contained in the table column TABLE["content of E1"].

My current approach looks like this (and does not work):

=COUNTIF(TABLE[E1]; E2)>0

I think excel searches for "E2" in the table headers and does not find anything. How can I approach this problem?

Answer 1

You can use the INDIRECT formula, eg:

=COUNTIF(INDIRECT("TABLE[" & E1 & "]"),E2)