In my attached spreadsheet, I have a table of taxing districts, and a table of properties within a portfolio. The table of properties indicates the taxing jurisdiction the property sits within. For example, one column indicates the name of the city, another the name of the county, the school district, etc.
I am trying to create a table that lists all of the tax districts across the top, and all of the properties down the side and then to generate a 1 or "TRUE" if a given property is located in a given tax district and a 0 or "FALSE" if it is not. Eventually, the resulting table will be multiplied by a table of tax rates (i.e., property tax, sales tax, etc.) to calculate the total taxes paid by property.
I have devised the below formula which does this, however, it does not spill automatically, and must be dragged down for the number of properties. However, properties are added and subtracted from the portfolio regularly, and I would like this to automatically calculate when a new property is added.
Here is what I have thus far:
=LET(
Ctd, COUNTA(Taxing_Districts[Name]),
TDNames, TRANSPOSE(MAKEARRAY(Ctd, 1, LAMBDA(r,c, INDEX(Taxing_Districts,r,2)))),
Ngeo, SEQUENCE(1,COLUMNS(Geography)),
Mgeo, MATCH(Ngeo, Ngeo),
LAMBDA(X, ISNUMBER(MATCH(TDNames,INDEX(Geography,X,Mgeo),0))*1)(MATCH($A2,Geography[Lot ID'#],0))
)
As you can see, I have a one-dimensional array "TDNames" which serves as my lookup value in my MATCH function. I am matching those values against the names in the "Geography" table, row by row. I did this by using a LAMBDA (a bit overkill, I know), and using a parameter, 'X', which is fed a value that matches the value in column A of my results table to the list of ID numbers in the "Geography" table. Thus, X = 1 in the first row, X = 2 in the second, etc.
I tried doing this where I use the entire "Geography" table in the Match formula, by placing a MATCH(SEQUENCE(N), SEQUENCE(N)) formula (similar to my variable "Mgeo" except based on the number of rows in the "Geography" table, which is equal to the number of properties in the portfolio) in for the value of 'X' but the result was all zeros. I think this is because it is trying to match the entire "TDNames" array against each row in the "Geography" table, and only results in a 1 if all items match across the entire row, rather than just matching the individual items within each row.
I have also tried numerous other ways, including MAP, BYROW and other means, but have not yet figured out how to get this to work where it will generate the resulting table with just a single-cell formula.
I have attached a file that shows the two input tables and the existing table of results that I have. The Taxing Districts table is located in the "Districts" tab, the Geography table is in the "Properties" tab and my current output is in the "Results" tab.
How do I amend my algorithm so that it will generate the table in my "Results" tab with just a single-cell formula that will automatically adjust in size when properties are added or removed from the portfolio?
