Search code examples
if-statementgoogle-sheetsvlookupflattengoogle-query-language

Error Query, and xlookup formula in dropdown list

Someone in this group had created a google sheet file for me. and I have given some names in the list in that google sheet files. Can you prepare the appropriate code to get the result through that given name? The person who made this cannot be contacted. It seems that the person is busy. I am just learning Google Sheets Coding. I am sharing google sheet link. Can you code it and make it ready?

spreadsheet sample

CODE:

=QUERY(ALL!B3:K,"where D like '" & 
XLOOKUP(A2,Conditions!A1:A,Conditions!B1:B) & "%' AND" & IF(A3="FEE NOT PAID"," K like '"," H like '") & 
XLOOKUP(A3,Conditions!D1:D,Conditions!E1:E) & "%'",0)

DROPDOWN LIST items:

(ALL PENDING LICENSE APPLICATION, ALL ISSUED LICENSE APPLICATIONM, ALL REJECTED LICENSE APPLICATION, ALL SUSPENDED LICENSE, ALL ISSUED NEW LICENSE NO)

enter image description here

Solution

  • try:

    =ARRAY_CONSTRAIN(QUERY({ALL!B3:K, FLATTEN(QUERY(TRANSPOSE(ALL!H3:K),,9^9))}, "where 2=2 "&
 IF(A2="",,IF(REGEXMATCH(A2, "ALL"), " and Col3 is not null", 
 " and Col3 contains '"&VLOOKUP(A2, Conditions!A1:B, 2, )&"'"))&
 IF(A3="",," and Col11 contains '"&VLOOKUP(A3, Conditions!D1:E, 2, )&"'")), 9^9, 10)

    enter image description here