I'd like to create a lookup from a dropdown with all unique sheet data that finds a match within any column and returns the selected row items.

Let's say that each row represents a single device with multiple components described in each column with a unique ID for all. How could I search for any ID and get all column IDs associated with that row selection?

I can do this for a specific single column or row with QUERY, VLOOKUP or INDEX, MATCH but perhaps there is a way to make this work for all without a single parent key.

Sample of Goal


You want to return a row based on a match in any cell from a dropdown value.

A query will generate the data that you need. Its design is an extension of:

=query(A2:D4,"select A, B, C, D where (A= '"&$F$12&"' or B= '"&$F$12&"' or C= '"&$F$12&"' or D= '"&$F$12&"') ",1)

In this answer:

  • Cell F12 contains the dropdown
  • where clause consists of four logical conditions (one for each column) each equal to the dropdown value in F12
  • or applies for each condition

Screenshot enter image description here

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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