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
– s1624210, Commented Sep 24, 2020 at 12:28

SJR · Accepted Answer · 2020-09-24 12:38:12Z

5

You can use the INDIRECT formula, eg:

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

answered Sep 24, 2020 at 12:38

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel structured reference with variable name

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related