I want to query a set of data (multiple rows and columns) and return the rows of data based on an input value (search box) - google sheets

Question

Raw data I want to query:

I want to query this data based on an input value that could feature in any of the columns of data to then return the correct rows of data:

For example if I type in 'A001' as the ID the result will return that row of data with headings, the same as if I typed in 'Desk 1' into my search box:

The challenge I am having is that I can query only against a single column of data rather than across all - help is much appreciated!

=query('RAW ASSET DATA'!A1:D,"SELECT * WHERE LOWER(A) LIKE LOWER(""%"&B2&"%"")",1)

Answer 1

Try this formula:

=query($A$1:$D$10,"select * where Lower(A) contains '"&H2&"' or A contains '"&H2&"'",1)

---

There are two WHERE conditions joined by OR. So either one or the other will apply.

#1: Lower(A) contains '"&H2&"' - compares the Lowercase value of Column A (the ID). So if the value of H2 is "a", then this condition will be executed

#2: A contains '"&H2&"' - compares the actual (Uppercase) value of Column A (the ID). So if the value of H2 is "A", then this condition will be executed.