IF, LEFT, INDIRECT Excel function for PLSS section-township-range

Question

I am a GIS specialist for a cadastral mapping company.

I'm running Office 2010 and I'm trying to build a function in Excel that derives the section, township and range from the 16 digit parcel identification number (PID) into individual columns.

The PID begins with the 3 digits of the survey township followed by the two section number digits. However the "survey township" should not be confused with the township and range of the PLSS. However, each "survey township" falls within the grid of a single township and range square in the PLSS.

Each survey township is made up of 9 "map sheets" from the old days of milar map sheets which held 4 sections per map. There are 36 sections in a "survey township". Nine map sheets with 4 sections per sheet is the 36.

SO.  PID 0241800000001000 

Survey township: 02
Map sheet:       4
Section:         18

PLSS township: 04
PLSS range   : 06

So far. I can easily gather the section number. I'm now working on the township and range. I created a reference list in sheet2 that lists every map sheet in the county (011-209). The particular county has 20 survey townships. The formula needs to refer to the list on sheet 2 and if the first 3 characters of the PID for survey township 2 are 021-029, the output for the township cell should be (04S) for township 4 South. The survey township range (021-029) and the PLSS township (04S) output are my variables, which I will alter per county.

Here's what I have so far:

=IF(LEFT(B2,3) = ("Sheet2!A1:A9"),"05"), IF(LEFT(B2,3) = ("Sheet2!B1:B9"), "04")

On Sheet 2,

• A1:A9 is referencing the mapsheet list for survey township (011-019)
• B1:B9 is referencing the mapsheet list for survey township 02 (021-029)

B2 is the PID location.

The output keeps either telling me "FALSE" or !VALUE#. Or it fails with an error.

Answer 1

Aside from winning the award for "Most Confusing Problem Statement" (please read comments from @Scott), I think you want to match the PID/township values to a list in Sheet2.

The IF(LEFT(B2,3) = ("Sheet2!A1:A9"),"05") is wrong, but I can tell you're trying to get a "04" or "05".

Instead of having these "groups" in separate columns, try putting all the possible values of the PID in column A (still Sheet2). Now, put the result you want for those values in column B. Should look something like:

 A  |  B
----+----
011 | 04
012 | 04
013 | 04
014 | 04
015 | 04
016 | 04
017 | 04
018 | 04
019 | 04
020 | 
021 | 05
022 | 05
023 | 05
024 | 05
025 | 05
026 | 05
027 | 05
028 | 05
029 | 05

Now you have a lookup table. In which the following formula should get you what you want:

=INDEX(Sheet2!$B$1:$B$19,MATCH(LEFT(B2,3),Sheet2!$A$1:$A$19,0))

And yes, tables can be your friend here (especially on Sheet2). But again, I didn't have an example of the table. Would love to help more, but we need more information.

Answer 2

Reading the description again, looking for answers to my earlier questions, I have concluded that each survey township not only falls within the grid of a single township and range square in the PLSS, but that it also matches it, except for the nomenclature. Thus, all 9 sheets of survey township 02 correspond to the old 9 sheets of Township 04S Range 06W (or 06E). Since you said that the current county has 20 survey townships, I created a hypothetical "map" of the country, showing it overlay of 20 survey townships onto a PLSS grid. (Since you have the maps, and know the area, you can adjust my example to suit.) I made this hypothetical map cross both a baseline and a meridian so that you would see its application in that eventuality.

This is the "map" I used for the example.

Converting that map into data for your Sheet2 generates the following sheet:

Using your sample data for PID 0241800000001000 I created this as Sheet1:

The formula for cell B2 is =LEFT(A2,2), to extract the survey township number from the PID.

The formula for cell C2 is =MID(A2,3,1), to extract the map sheet from the PID.

The formula for cell D2 is =MID(A2,4,2), to extract the section number from the PID.

Using the last formula as a guide you should be able to extract any other part of the PID that is positionally predictable. I'm guessing that the remaining 10 digits are in two groups of 5 for sectional divisions east and north of the sectional corners. (8,000 Gunter's links would work, I guess, if you're in a place that still uses that system, or even inches fits in 5 digits.)

The formula for cell E2 is =VLOOKUP(VALUE(B2),Sheet2!$A$2:$C$21,2), to find the value (numeric) of cell B2 in column A of Sheet2 and return the value of the 2nd column in the same row. Note, this is the one point to be carefull! The values in the indexing column of Sheet2 must be numbers, not text. So instead of "02" it's only 2. This is probably better, since when you type numbers Excel wants to store them as numbers, not text, unless you force the issue with quotes or explicit cell formatting.

The formula for cell F2 is =VLOOKUP(VALUE(B2),Sheet2!$A$2:$C$21,3), except that it's the third column, this works the same as the formula in cell E2.

Modifying the data in Sheet2, and the reference to it in columns E and F of Sheet1 should allow you to reuse this for any county in your state, as long as they use the same system with the PID. I hope, for the points I misunderstood, that you will be able to adjust the concepts given here to correct them.

EDIT

To use the same system for multiple counties, or Survey Township sets, modify Sheet2 by inserting two columns at the beginning. This is for the list of counties, or other names you want to use for Survey Township sets, and their corresponding 'range' for finding PLSS data. This list must be kept alphabetized as other sets are added. Each county will have its own set of three columns. These tables don't need to be stacked left to right. They can be arranged in any manner that fits best in your workflow. They also need not be kept in any order, only the names in column A have to be in order. This is a sample with 3 sets, which I've colored only to make it obvious how they break down.

This is the sample "map" of the sets. I included some overlapping areas since I know not all county lines will follow the conveniently drawn PLSS boundary lines. So, I'm guessing that two different Survey Townships could correspond to a common PLSS Township/Range.

In the first sheet, Sheet1, insert a new column for the County name as column A. Sample shown below.

The names used in this column have to match, exactly, the names used in the first column of Sheet2, but can be used in any order. The formulas of Sheet1 need to be adjusted for the changes in both sheets, and to reference the county names.

The formula for cell C2 is =LEFT(B2,2).

The formula for cell D2 is =MID(B2,3,1).

The formula for cell E2 is =MID(B2,4,2).

The formula for cell F2 is

`=VLOOKUP(VALUE(C2),INDIRECT("Sheet2!"&VLOOKUP(A2,Sheet2!$A$2:$B$100,2)),2)`.

The formula for cell G2 is

`=VLOOKUP(VALUE(C2),INDIRECT("Sheet2!"&VLOOKUP(A2,Sheet2!$A$2:$B$100,2)),3)`.

In cells F2 and G2 the formula includes the range Sheet2!$A$2:$B$100 where the 100 is just a number chosen to allow for a lot of counties (99 actually). If you're state has 120 counties, and you need to cover all of them, change the 100 to 121, for example.

The 'magic' happens in the last two cells, using two functions, VLOOKUP and INDIRECT. The inner VLOOKUP uses the county name in Sheet1 column A to find the range to use in Sheet2. This is combined with other string parts to create the full reference of the table in Sheet2. The INDIRECT function takes that string and turns it into a reference that the outer VLOOKUP can use.

You could use separate sheets to cover different states if you wanted to. To make that work change the Sheet2 references in cell F2 and G2 to Sheet3, or whatever the new sheet name is. You can also rename the sheets, even if it's only 2 of them, to something that works better. Again, you need to change the Sheet2 references to the new name. (Total of 4 replacements per line, two each in F and G.) If two states have the same county name, it won't matter since the name will be looked up in the list on that state's sheet, and reference its own table, not the same-named county in a different state.

One point I forgot to mention originally is that the formatting of the cells in Sheet1 for the PID has to be set to text, otherwise it will be understood to be numbers, and Excel will drop leading zeros, and the string manipuation functions in the next 3 columns will fail, which cascades into failure, or even errors, in the last two columns.

An extra note for people using Linux, the ! in the sheet references of VLOOKUP needs to be changed to a period, ., to make it work in LibreOffice Calc, but the rest of it stands as is.