Search code examples
regexgoogle-sheetsgoogle-sheets-formulaarray-formulastextjoin

How can I use ArrayFormula within a formula containing Vlookup, Filter and RegexMatch

I'm making a Google Spreadsheet which checks if a Value in Column A contains keywords out of a List in Column F. Problem is that I want to check if the value in A is exactly the same OR partly the same.

With a lot of help i've found over here I created this working formula:

=VLOOKUP(FILTER(ArrayFormula((LOWER(F:F)));REGEXMATCH(LOWER(A2);ArrayFormula((LOWER(F:F)))));ArrayFormula((LOWER(F:G)));1;FALSE)

Because I automatically import new lines of data I want to use ARRAYFORMULA. Unfortunately, I can't get it done.

This are my working formulas:

=VLOOKUP(FILTER(ArrayFormula((LOWER(F:F)));REGEXMATCH(LOWER(A2);ArrayFormula((LOWER(F:F)))));ArrayFormula((LOWER(F:F)));1;FALSE)

=VLOOKUP(FILTER(ArrayFormula((LOWER(F:F)));REGEXMATCH(LOWER(A3);ArrayFormula((LOWER(F:F)))));ArrayFormula((LOWER(F:F)));1;FALSE)

You can find my spreadsheet over here: https://docs.google.com/spreadsheets/d/1aIdQ65SdeXW-4cTr8azQIiLNGcRCvTexGS_lFu8mECs/edit#gid=1308644379

Solution 
  • =ARRAYFORMULA(PROPER(IFERROR(REGEXEXTRACT(LOWER(A2:A); LOWER(TEXTJOIN("|"; 1; F2:F))))))

    0