I'm trying to categorize a cell value in Google Spreadsheets, based on a categories table, so that depending on the value in the cell, the correct category will be determined.
Given a categories table like this:
A B Label Category bla 1 cat1 bla 2 cat2 ...
then VLOOKUP can find exact matches:
A B Description Category bla 1 =VLOOKUP(A10, A$2:B$6, 2, False) bla 2 =VLOOKUP(A11, A$2:B$6, 2, False)
However, VLOOKUP only works because the categories table contains exact matches of the possible values for the cell. What I want is the categories table to contain patterns:
A B Pattern Category bla.*1 cat1 bla.*2 cat2 foo cat1
This would be trivial to do using Google Apps Script where I could easily write a loop that checks for a match on any of a set regular expressions, but I'm curious to find how to do it using spreadsheet builtin functions.
The docs for VLOOKUP indicate to use Query if matching by pattern, but I don't think that Query can be used for this, because Query matches on a specific regexp, whereas my categories table has many. I tried "=query(A$1:B$6, select F where A matches G" but that does not work.
So is there a way to do this or is a custom function (using GAS) my only option? Basically I'm looking for a way to say "find the category in table for which the cell of interest (not in the category table) matches the category's regular expression".