Query formula in sheets turns into sublistlookup

[Chris_Bean1]

Hi folks,
I have a formula in a sheet that contains a query; it reads as follows:
=if($H156="","", query(‘Order details’!$D$2:$V,“Select V where LOWER(D) = '”&lower($B156)&"’ "))

It’s being turned into if(RC8="","", sublistlookup(index(‘Order details’!C22:C22,match(RC2,‘Order details’!C4:C4,0)),index(‘Order details’!C21:C21,match(RC2,‘Order details’!C4:C4,0)),RC8))

by Appsheet,

When a new row is added it doesn’t compute correctly.

Any suggestions? I’ve regenerated the structure in Data/Columns a couple of times to no avail
Thanks
Chris

[LeventK]

help.appsheet.com

Spreadsheet Formulas

How formulas you write in your spreadsheet impact your app creation process

help.appsheet.com

Mismatched Spreadsheet Formulas

Diagnosing Mismatched Formulas in a Column

[Chris_Bean1]

Thanks - but I have no mismatched formula warnings, and the R1C1 syntax appear to be correct…

[LeventK]

QUERY formula returns an array and expands to multiple columns and rows depending on the SELECT parameter. So with each row of the sheet coincides with the selection range. Why do you need that QUERY formula? Provided you wish to return only a single value from another table, you can use VLOOKUP - though you can handle that easily with AppSheet’s LOOKUP() expression as well where using spreadsheet formulas in the back-end might drastically affect app’s sync durration