Display selected row for match in any columns from a dropdown list in Google Sheets

Question

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.

Answer 1

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:

• WebApps question: Query syntax using cell reference and
• the design of the where clause by joining multiple conditions using ... logical operators.

---

=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