Search code examples
if-statementgoogle-sheetsconcatenationtextjoinifs

How to exact match withing column using QUERY in Google Sheets?

I have some values in the cell AA1 as

'SONA'|'SONAKSHI'|'SUMON'|'SUMONA'

But it gives result as

SONA'|'SONAKSHI'|'SUMON'|'SUMONA'

I have created a formula,

=let(cell,indirect($CA$27&"1"), ifs(cell<>"", " AND "&CC27&" MATCHES '.*("&UPPER(cell)&").*' " , cell="",""))

It gives result as

AND Col7 MATCHES '.*(SONA'|'SONAKSHI'|'SUMON'|'SUMONA').*'

' is missing in the starting. How to fix it. Please help me.

My expected result is

AND Col7 MATCHES '.*('SONA'|'SONAKSHI'|'SUMON'|'SUMONA').*'
Solution

  • My expected result is

    AND Col7 MATCHES '.*('SONA'|'SONAKSHI'|'SUMON'|'SUMONA').*'

    "matches '.*('"& UPPER(cell) &").*'"

    enter image description here

    enter image description here

    try:

    =IF(AG1 = "", LET(cell, INDIRECT($CA$27&"1"), 
 IFS(cell<>"", " AND "&CC27&" MATCHES '.*('"& UPPER(cell) &").*' " , cell="","")), )

    enter image description here