CODEWITHSUNDEEP
google-sheets
DeeKay789
DeeKay789

Reputation: 367

ArrayFormula to Validate and set array against a validation array

Thanks for taking the time to look at my question.

I’m struggling to find the right combination or arrayformula, vlookup, index/match pairing. My goal is to test each cell of a 1 column data array (type string) against a validation set of single or paired words in another 1 column array. If the cell in the data array contains a validation word then the function enters the validation word into the adjacent cell in the data array, If no match is found then the adjacent cell should be set to null. For testing my function I’m using data something like the following.

       Desc_Array                         Result Array Validation_Array

     | A                                 |     B      |    C     |
   __________________________________________________________________
1    | Lorem ipsum dolor sit amet        |  (null)    | Quis nisi|
2    | Consectetur adipiscing elit       |  Cons      |   Ut     |
3    | Sed do eiusmod tempor incididunt  |  Sed       |   Et     |
4    | Ut labore et dolore magna aliqua  |  Ut        |   Ad     |
5    | Ut enim ad minim veniam           |  Ut        |   Cons   |
6    | Quis nostrud exercitation ullamco |  (null)    |   Sed    |
7    | Laboris nisi ut aliquip ex ea     |  Ut        |          |
8    | commodo consequat                 |  Cons      |          |

The arrayformula function would be in cell B1

A few things to note in the results array. The test is TRUE if the validation word appears at the beginning of any word in the data array. The test can return TRUE on finding the first match. The test is not case sensitive

For the test, could I use a simple regexmatch expression?

=if(Desc_Array<>"",regexmatch(Desc_Array,"\A(?i)"&Validation_Array),(NOT(ISBLANK(Desc_Array))))

I tried a QUERY “contains” method but it fails test 1.

I think I’m after something like:

=ArrayFormula(iferror(index(Validation_Array,match(Validation_Array,Desc_Array,FALSE),1),))

Upvotes: 1

Views: 386

Answers (1)

Max Makhrov
Max Makhrov

Reputation: 18717

=TRANSPOSE(SPLIT(TEXTJOIN(",",1,ArrayFormula(IFERROR( REGEXEXTRACT(A1:A8, "(?i)[^a-z\-]("&JOIN(")|[^a-z\-](",C1:C6)&")|^("&JOIN("|",C1:C6)&")"), "(null)"))),","))

Notes:

C1:C6 = Validation_Array

A1:A8 = Desc_Array

Upvotes: 1

Related Questions