Search code examples
if-statementgoogle-sheetsgoogle-query-languagenested-ififs

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

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.

enter image description here

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.

enter image description here

Is this possible with Google Sheets?

Thanks, for any help you can offer.

Solution

  • use:

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

    enter image description here

    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.")

    enter image description here