Reference a column in another sheet using column name (heading) in Google Sheets inside an ArrayForm-CodePudding

I have an example sheet at

or like:

={"Count of one Properties"; ARRAYFORMULA(IF(A2:A<>"", 
 COUNTIFS(properties!A2:A, A2:A, 
 VLOOKUP(ROW(properties!A2:A),{ROW(properties!A2:A), properties!A2:Z}, 
 MATCH("type", properties!1:1, 0) 1, 0), "one"), ))}

CodePudding user response：

not sure if you are familiar with "named ranges":

={"Is Special"; ARRAYFORMULA(IF(A2:A<>"", REGEXMATCH(FirstName, "b|d|e"), ))}

update:

={"Is Special"; ARRAYFORMULA(IF(A2:A<>"", 
 REGEXMATCH(QUERY(FirstName, "offset 1", 0), "b|d|e"), ))}