There is a better way to approach this genealogy index, and it starts with rethinking how you use spreadsheet formulas. The user's instinct to replace manual eyeballing with a formula is correct, but the specific IF/AND approach they tried is fighting the very structure of their data. The core issue isn't the logic; it's that they are trying to force a single cell to make a decision about multiple page numbers at once, when the spreadsheet is already capable of handling each number individually. The solution is to break the problem into smaller, repeatable steps, not to nest more conditions into one formula.
What the user actually needs is a two-part process. First, split the page numbers into separate cells, one per number, using the text-to-columns feature or a simple formula. This gives you a clean, numeric list for each person. Then, for each of those individual page numbers, use a lookup or a simple IF statement to assign the correct issue number based on the page ranges you already know. For example, if pages 1-40 are issue 1, 41-80 are issue 2, and so on, you can write a formula that checks each page number and returns the issue. Once you have that issue number for every page, you can concatenate them back into a single cell with a formula like TEXTJOIN, which will give you the "1,3,4" result you're after. The key is to let the spreadsheet do the heavy lifting on each page number separately, not to try to evaluate all of them at once.
The user's frustration is understandable, and their willingness to share the messy details of their project is exactly what makes this kind of problem solvable. But the real takeaway is that spreadsheets reward a modular approach. You don't need a single, clever formula that does everything. You need a series of straightforward steps that each handle one piece of the puzzle. Once you stop trying to make one cell do all the work, the logic becomes clearer, and the formula becomes more reliable.
Start by splitting the page numbers into separate columns. Then assign issue numbers to each page using a simple lookup table. Finally, combine the issue numbers back into one cell. This isn't just a fix for this specific genealogy index; it's a mindset that applies to any messy data-cleaning task. Break it down, handle each piece, and then bring it back together. The spreadsheet is a tool for thinking, not a magic wand. Use it that way, and you'll get the result you need without the headache.