Reference a column in another sheet using column name (heading) in Google Sheets inside an ArrayFormula

user_3383907 0
Viewed 90

I have an example sheet at https://docs.google.com/spreadsheets/d/1i96cjWWGwLQQd2enqQhLylFo31n5GyxbCTcTEPk2I2A/edit#gid=0.

I have two parts to my question.

Part 1

In the names sheet, in C1, I have this formula:

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

The formula works as expected but I do not like the B2:B reference because it is not easily obvious what column I am checking against. I'd rather refer to the column by it's name (First Name in this case). Is that possible? Can I replace B2:B with some formula or function that lets me lookup the column range by searching for First Name? I know I can use MATCH to get the column number for First Name but I'm not sure how to put that into this formula -- especially since I cannot use INDRECT in an ARRAYFORMULA.

Something like:

={
    "Is Special";
    ARRAYFORMULA(
        IF(
            A2:A <> "",
            REGEXMATCH([some formula or function that lets me lookup the column range by searching for "First Name"], "b|d|e"),
        )
    )
}

Part 2

Similar to above, on the names sheet, in D1 I have this formula:

={
    "Count of one Properties";
    ARRAYFORMULA(
        IF(
            A2:A <> "",
            COUNTIFS(
                properties!A:A, A2:A,
                properties!B:B, "one"
            ),
        )
    )
}

The formula works as expected. However, I do not like the properties!B:B reference. Reading the formula it is not obvious what column from the properties sheet I am checking against. Is it possible to something similar like above where I get the column range by searching for the column name, in this case type?

google-sheets match filter syntax arrays
2 Answers

if you dont like named ranges try:

={"Is Special"; ARRAYFORMULA(IF(A2:A<>"", 
 REGEXMATCH(FILTER({A2:B, D2:E}, {A1:B1, D1:E1}="First Name"), "b|d|e"), ))}

enter image description here

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"), ))}

enter image description here

user_56326290

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

enter image description here

enter image description here

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

enter image description here

update:

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

enter image description here

user_56326290
Related