Lookup multiple columns in Google Sheets and return a list from the matches

Question

I think the title accurately describes what I'm trying to achieve.

https://docs.google.com/spreadsheets/d/1sRQzXXZ4a3vjAwvsH4rrWfijxV4jCl_OV3iQO00yvlQ/edit?usp=sharing

Essentially, I have a table of data for houses, the street it's on, whether it has a pool or gates etc. and I'm trying to create a lookup in Google Sheets so if someone is trying to find a house with a pool for a maximum of $800k then I can return results that match the criteria.

This is how the table data looks.

I want to be able to query the data here in columns D, E, F, G (G being a maximum value in the lookup) and return the data in columns A, B, C if everything matches.

I would enter on a different tab, the maximum budget (which would need to do a max lookup of column G, and then look for any Y/N in the other columns and return a list of all matches.

Is this possible with Google Sheets?

Thanks, for any help you can offer.

Answer 1

use:

=QUERY(Houses!A:I, 
 "select C,B,A,H 
  where H <= "&B3&" 
    and D = '"&B4&"' 
    and E = '"&B5&"' 
    and F = '"&B6&"'", 0)

---

update:

=IFERROR(QUERY(HousingData, 
 "select C,B,A,G 
  where G <= "&B3& 
  IF(B4="Y", " and D = '"&B4&"'", )&
  IF(B5="Y", " and E = '"&B5&"'", )& 
  IF(B6="Y", " and F = '"&B6&"'", )&
  IF(B7="Y", " and J = '"&B7&"'", ), 0), "No houses found.")