Log In
Register
Home
New Member
March 11, 2022
Offline
Hello
I am helping a client by creating a worksheet for the public to use to figure out which landscaping buffer rules apply to their development site. I need excel to spit out the type of required landscaping buffer required based on a user's entry of the zoning of their lot and the zoning of an adjacent lot.
There are 7 different zoning districts for every lot in town: R-30, R-20, OI, MM, TC, I-1, or PD. A lot may border lots in the same or in a different zoning district.
There are 4 kinds of landscaping buffer: A, B, C, or None.
I need a way for the applicant to enter the zoning district of their lot and then enter the zoning district of an adjacent lot and have the worksheet identify the kind of landscaping buffer required between the two (it will always be an A, B, C, or None). If a lot is zoned R-30, it has to provide a Type A buffer if bordered by an R-20 lot, a TC lot, or an I-1 lot. No buffer is required between an R-30 lot and an OI lot, a MM lot, or a PD lot. So for this R-30 lot example, the required buffer will be either a Type A or None based on the adjacent zoning.
It seems like this is an if/then or an ifs equation, but it has to work on text (not numbers) and it seems like I need an "and" operator to capture the range. Obviously I have no clue what I am doing and need some help to figure out how to structure the equation for this simple determination. Any help would be deeply appreciated by me and the general public. Thanks!
October 5, 2010
Offline
Hi Chad,
You can do this with a combination of data validation, tables (for lookup) and this formula
=XLOOKUP(D4,ZoningDistricts[Zoning Districts],FILTER(Buffer[Buffer],Buffer[Lot District]=B4))
The tables hold the data that are needed to lookup what is input by the 2 data validation cells where the user chooses the zoning district of their lot and the adjacent lot
Tables are set up like this
The right-most table, lists the the buffer types corresponding to the combination of lot and adjacent lot, in the same order as the buffer types are listed in the left most table. This is important because they must be in the same order so that the lookups work correctly.
You'll see in the right most table that I've entered R-30 and dummy data for R-20. Hopefully this makes it clear how you can expand this table for the other lot type combinations.
The formula works by using FILTER to return an array of only the buffer types for the district chosen in cell B4 (using data validation).
XLOOKUP then uses the value in cell D4 (entered using data validation again) to pick out the corresponding buffer from this FILTERed array
See the attached workbook for this example
Regards
Phil
1 Guest(s)
