I am having trouble assigning category Values in a table according to wildcards in another table. There seem to be no function in calc that would help me to solve this problem.
Example:
Data Table (bold values in the category column are inserted by a function)
| Sentence | Category |
|---|---|
| This is an BMW model | Car |
| This Phillips is for the kitchen | Kitchen applience |
| This is Synology device | NA |
Assignment Table
| Search Term | Category |
|---|---|
| BMW model | Car |
| Phillips | Kitchen applience |
| * | NA |
So what I want to achieve is similar to VLOOKUP but the search term (lookup value) and the text must be in the opposite tables.
Is this achievable with only calc functions or do I need a macro for that?
Any help, hints or links to existing solution is much appreciated!
UPDATE: Those tables are on a separate Sheets.
Assume "This is an BMW model" is in cell A2 and "BMW model" is in cell C2. Enter the following formula in cell B2 and drag to fill down.
=IFERROR(INDIRECT("R"&SUMPRODUCT(COUNTIF(A2:A2;"*"&C$2:C$3&"*")*ROW(D$2:D$3))&"C"&COLUMN(D1);0);"NA")
Result:
Car
Kitchen applience
NA
Breakdown:
IFERROR(...;"NA") - If no matches are found, then the row will be 0, causing INDIRECT to give an error.INDIRECT("R"&...)&"C"&COLUMN(D1);0) - Using R1C1 notation, get the value of the cell in the category column.SUMPRODUCT(...) - Get the results of array calculations and combine them into a single value.COUNTIF(A2:A2;"*"&C$2:C$3&"*")*ROW(D$2:D$3) - Here is the core of our formula. If A2 matches a regular expression such as "*" & C2 & "*" then mutiply it by an array containing the row number. So a match will multiply 1 by the row number of the search term, while the other row numbers will be multiplied by 0 and therefore ignored in the final sum from SUMPRODUCT.If multiple matches in one sentence are possible, then the formula would need to be modified to handle that.