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.
![enter image description here](https://cdn.statically.io/img/i.sstatic.net/Pxh5G.png)
Converting that map into data for your Sheet2 generates the following sheet:
![enter image description here](https://cdn.statically.io/img/i.sstatic.net/yyP5A.png)
Using your sample data for PID 0241800000001000 I created this as Sheet1:
![enter image description here](https://cdn.statically.io/img/i.sstatic.net/FPLWa.png)
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.
![enter image description here](https://cdn.statically.io/img/i.sstatic.net/gTBgW.png)
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.
![enter image description here](https://cdn.statically.io/img/i.sstatic.net/5Lx5o.png)
In the first sheet, Sheet1
, insert a new column for the County name as column A
. Sample shown below.
![enter image description here](https://cdn.statically.io/img/i.sstatic.net/dtf4b.png)
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.
=TEXT(LEFT(B2,2)+4,"00")
this should give you the