I’m combining multiple sheets with IMPORTRANGE and then querying them. The issue is with Col3, which contains IDs like:
ABC10000000001 ABC10000000001 ABC10000000001 or 52685485 59521458 55152655
=QUERY(
{
IMPORTRANGE(B1,"sheet1!A2:K10000");
IMPORTRANGE(B1,"Sheet2!A2:K10000");
IMPORTRANGE(B1,"Sheet3!A2:K10000")
},
"select Col1, Col7, Col2
where Col3 contains '" & B3 & "'
and Col6 = date '" & TEXT(C3,"yyyy-mm-dd") & "'
and Col2 contains '" & D3 & "'
label Col1 'Insurance Name', Col7 'Measure Name', Col2 'Name'",
0)
When I filter with a numeric value in B3 (e.g. 56), the query works. But when I try to match the full alphanumeric ID (e.g. KUC130589653), it fails — no matches are returned.
How I can fix it and I want to be able to search/filter individually by three optional criteria:
The problem is most likely with the way query() treats data that mixes numbers and text in the same column. The function will only accept one data type per column, so if your data columns contain a mix of text and numbers, or the occasional date or Boolean, the majority type will rule, and other types will be returned as nulls.
One way to make it work is to convert all data to text, like this:
=query(
arrayformula(to_text(vstack(
importrange(B1, "Sheet1!A2:K"),
importrange(B1, "Sheet2!A2:K"),
importrange(B1, "Sheet3!A2:K")
))),
"select Col1, Col7, Col2
where Col3 contains '" & B3 & "'
and Col6 = '" & text(C3, "yyyy-MM-dd") & "'
and Col2 contains '" & D3 & "'
label Col1 'Insurance Name', Col7 'Measure Name', Col2 'Name' ",
0
)
Note that the date column Col6 is also treated as text, so the "yyyy-MM-dd" bit should match the date format in the imported data.