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?

@SJR Let's say E1 is "Name" and E2 is "Max". I'd like to check whether `TABLE[Name]` contains "Max". — s1624210, Sep 24 '20 at 12:28

score 5 · Accepted Answer · answered Sep 24 '20 at 12:38

5

You can use the INDIRECT formula, eg:

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

answered Sep 24 '20 at 12:38

SJR

22,986
6
18
26

Excel structured reference with variable name

1 Answers1