Assign matching column header from table with match criteria to a string list in an arrayformula in Google Sheets

Question

In a Google Sheet I have a first tab with data entries in column A. I would like to assign a category to each entry in column B using an arrayformula. The category is supposed to be determined in the following way: If in the second tab one of the strings of a column matches the entry, the header of the column is supposed to be assigned as the category.

I have the feeling that there should be a way to do this with the query function, but can't figure it out.

This is an example sheet. I am happy to slightly change the setup of the sheet if the solution requires it but would like to avoid blowing up the sheet. Also I am interested in the simplest possible solution.

Answer 1

Approach

I would use a custom function in this case so that you can gain more control on the logic and structure of your sheet. First you should change move the categories sheet into a rows structure since Google Sheets gets the values in that way.

Now let's build the function that extracts the data from the categories and assign every name to the corresponding one.

This is a custom function so I build the proper docs header so that it will be visible in your Spreadsheet context.

/**
 * Assign the name the corresponding category.
 *
 * @param {input} the name to assign to the category
 * @return The matching category
 * @customfunction
 */
function MATCHCATEGORY(input) {
  // if the input is from ARRAYFORMULA I will reiterate along the array with a map
  if (input.map) {
    return input.map(MATCHCATEGORY)
  } else {
    // Recursion base case
    // I will get the categories rows
    var rows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CATEGORIES").getDataRange().getValues();
    var category = "";
    // Now I will map a string matching function along the rows 
    rows.map(row => {
        let no_blanks = row.filter(value => value != "");
        for (var i=1; i<no_blanks.length; i++) {
            if (input.includes(row[i])) {
                category = row[0];
                break;
            }
        }
    });
    // Finally I return the category
    return category
  }
}

Now we can use our custom function inside an ARRAYFORMULA:

=ARRAYFORMULA(MATCHCATEGORY("ENTRY COLUMN RANGE"))