0

Raw data I want to query:

Raw Asset Data

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:

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)

1 Answer 1

0

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.

2
  • Thanks @Tedinoz - exactly what I was looking for!
    – AndyB
    Mar 15, 2022 at 2:24
  • Pleasure. You might consider “accepting” this answer.
    – Tedinoz
    Mar 16, 2022 at 7:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.