Assign Category Values according to wildcards in assignment table

user_491320 0
Viewed 36

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.

openoffice-calc vlookup
1 Answers

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.

user_51005640
Related