Referring to a Table column by it's column number

[mark hansen]

I have a list of column headers and I need to count the amount of times "No" shows up in a column for a given date range and person name. I have this formula, which works well.
=COUNTIFS(Table1[Date of Visit],">="&$AY$32,Table1[Date of Visit],"<="&$AY$33,Table1[Nurse name],$BA$31,Table1[Pediatric Question 1],"No")

The "Pediatric Question 1" is the header name as well as the label name to indicate which column the count is for. How would I change " Table1[Pediatric Question 1],"No" " so the column header name is picked up from the same name used as a label name. In my searches for this, I have found, " =MATCH($AY35,Table1[#Headers],0) " which returns the number 7, which is the column number for Pediatric Question 1. (AY35 is where the column label name is.)

How can I incorporate the match statement to indicate the column to count the word "No" in the column with the header in AY35?

I can go down the list of column header labels changing the formula to the new column headers, but there should be a better way.

Thanks for any ideas.
Mark

 

[Fluff]

How about
=COUNTIFS(Table1[Date of Visit],">="&$AY$32,Table1[Date of Visit],"<="&$AY$33,Table1[Nurse name],$BA$31,index(Table1,0,Match($AY35,Table1[#Headers],0)),"No")

 

[mark hansen]

Thanks for the quick reply Fluff. That did it!!

 

[Fluff]

You're welcome & thanks for the feedback.