0

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.

player0
  • 124,011
  • 12
  • 67
  • 124
Adam
  • 13
  • 1
  • Kindly add input table and expected output table as **text table** (NOT as IMAGE) to the question. [Click here](https://webapps.stackexchange.com/a/161855/) to create a table easily. Adding such tables greatly increases your chances of getting a elegant answer, as **it is easier to copy/paste**. If you share spreadsheets, your question maybe closed, as questions here must be [self contained](https://meta.stackoverflow.com/a/260455). Your table should be a [mre].[Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), if you share Google files. – TheMaster Oct 14 '22 at 12:32

1 Answers1

0

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

player0
  • 124,011
  • 12
  • 67
  • 124
  • Sorry, me again. I guess my requirements weren't great. I actually wondered if it was possible to make some of this conditional. For example, while someone might want a pool, I'd like it so that if a house has a pool, but they say it's not important, it still shows. So I guess the condition is: If pool = Y then search and return where D = Y else return the result Meaning if pool = N then return Y and N. I cannot wrap my head around this logic. – Adam Oct 14 '22 at 13:20