Returning the next & previous result from a table
Our take
This user's challenge perfectly illustrates a common bottleneck in traditional spreadsheet workflows: the struggle to handle dynamic, account-specific business day logic efficiently. The complexity of nested lookups across date and account dimensions exposes limitations in tools designed for simpler tabular tasks. As seen in discussions like Table of instances occurred and Nest if statement matches but not returning correct data, users increasingly grapple with scenarios where formulaic approaches become unwieldy, error-prone, and ultimately unsustainable for real-world data relationships involving time and unique business rules.
The significance here extends beyond a single formula request. It represents a fundamental friction point in data management: the inability of legacy systems to natively support context-aware temporal logic. Manually creating and maintaining separate holiday schedules for dozens of accounts within a static grid forces users into reactive firefighting, as demonstrated by the frequent support queries about errors like Error deleting columns and rows in excel. This scenario highlights a broader need for platforms capable of understanding not just data points, but the *relationships* between them – like how an account's unique holiday calendar dictates its valid business day sequence. Traditional spreadsheets, reliant on manual formula construction and rigid table structures, inherently lack the agility to model such dynamic, interconnected temporal systems without introducing significant overhead and risk.
Addressing this requires more than just clever formula combinations; it demands a shift towards data systems designed for inherent flexibility and context-aware computation. The future lies in platforms that can automatically infer and apply account-specific business day rules, generating next and previous dates as core, low-friction functions rather than complex manual workarounds. Imagine simply referencing an account and a date, with the system intelligently navigating its unique calendar context to return the required adjacent business days. This transformation empowers users to focus on insights and decisions, not on constructing fragile lookup chains. As data complexity grows, the ability to naturally handle these temporal relationships will define truly productive, human-centered data experiences. The question becomes: how can we evolve beyond static grids to build systems that inherently understand time as a dynamic, contextual variable?
Hi, I have a specified date (say 01/06/26 in cell A1), and a specified account (say account "7032" cell B1, I'd have a list of up to 50 accounts say from B1 - B50 but I'd drag the formula down to produce a result for each separately). I have automated a table in a tab called "Business Days", the table shows all the days in 2026 in Row1, and all the accounts we track in Column A. The contents of the table C1:NC500 contain either "Y" (if it is a business day) or "N" (if it isn't). I need two formulae - one that would lookup the date from A1, and the account from B1, check the table and return the next date that contains a "Y", and one that would return the previous date that contained a "Y". So I'm starting with a list of accounts that were on holiday for 01/06/26 and returning what are the dates for the next, and previous business days for each of those accounts separately (the different accounts track different holidays, hence the table). I'm thinking some combination of lookup, index, match, & small, but it's beyond my skills.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience