In Google sheets, how do I have a cell return the value of cell A, based on the values of cells B, C and D?

Question

I'm trying to make a simple database that returns a value based on a number of different criteria.

Here is the Search Page they'll use to access the data:

They choose from one or more criteria in row 2 (text types, up to three tags, or reading level); all other cells will be protected. I want their results to appear in a list from A3 down (I've already set up the sheet to populate the rest of the rows based on the data in the A column).

Here is the Database so far. I want the Search page to draw from column A, based on the criteria in the text types, tags, and/or reading level columns.

Any assistance would be greatly appreciated!

Answer 1

You want a search screen that allows you to select titles based on certain criteria, including Text Type, 3xTags and Reading Level.

It is likely that your question could have several answers. Please consider this as one possible solution.

Dropdown cells

These need to be built with Data Validation. Since these are displayed in the screenshot of your question, we can assume that you have done these already.

• Text-Type: Cell C2
• Tags: Cells D2, E2 and F2
• Reading Level: G2

Query

Insert this formula in cell A5.
Note, the query assumes that the raw data and the enquiry screen are on different sheets. The raw data is assumed to be on Sheet="data" - edit this sheet name according to your own setup.

---

=iferror(QUERY({data!A2:H7},"select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8 
     where 
     (" & IF($C$2="All", "1=1", "Col3 contains '" & $C$2 &"'")& ") 
     and 
     (" & IF($G$2="All", "1=1", "Col7 contains '" & $G$2 &"'")& ") 
     and 
     (" & IF($D$2="All", "1=1", "(Col4 contains '" & $D$2 &"' or Col5 contains '" & $D$2 &"' or Col6 contains '" & $D$2 &"')")& " 
   OR " & IF($E$2="All", "1=1", "(Col4 contains '" & $E$2 &"' or Col5 contains '" & $E$2 &"' or Col6 contains '" & $E$2 &"')")& " 
   OR " & IF($F$2="All", "1=1", "(Col4 contains '" & $F$2 &"' or Col5 contains '" & $F$2 &"' or Col6 contains '" & $F$2 &"')")& "
     )
    ")
,"No titles meet these criteria.")