Spreadsheet Lookup Indexing with Binary Search and Caching
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Lookup functions in spreadsheet applications, such as VLOOKUP and MATCH, become computationally expensive when the data set is not sorted by the value type referenced in the function, leading to intensive processing costs and time, especially when multiple functions overlap or share ranges.
Innovation Solution
Generating a sorted index for the lookup function range using binary search operations, allowing for fast searches by sorting row or column numbers based on the value type, and caching this index for subsequent use to reduce redundant processing.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If a lookup function is applied to data sorted by a value type that does not correspond to the reference value type, then the lookup function can still return results, but the processing cost and time become expensive
Solution Approach 1:
The patent creates a cached index that pre-sorts row or column numbers based on the reference value type before the lookup function is executed. This preliminary sorting action allows the lookup function to operate efficiently on pre-organized data, eliminating the need to process unsorted data during the actual lookup operation.
Solution Approach 2:
The patent introduces a cached index as an intermediary data structure between the original data set and the lookup function. This cached index acts as a mediator that translates the unsorted original data into a format suitable for efficient lookup operations, allowing the lookup function to work with pre-sorted row or column numbers.
2Adaptability or versatility
If multiple lookup functions overlap or share ranges, then comprehensive data search is achieved, but processing costs increase intensively
Solution Approach 1:
The patent merges multiple lookup functions that share overlapping ranges by creating a single cached index that serves all of them. Instead of processing each lookup function separately on the original unsorted data, the system combines their requirements and generates one cached index that satisfies all overlapping ranges, significantly reducing redundant processing.
Solution Approach 2:
The cached index is designed to be universal and multi-functional, serving multiple lookup functions simultaneously. A single cached index can be reused across different lookup operations with overlapping ranges, allowing the same data structure to perform multiple search functions without regeneration.
3Speed
If a sorted index is generated for each lookup function range, then fast searches are enabled, but redundant processing occurs when ranges overlap
Solution Approach 1:
The system performs preliminary analysis to identify overlapping ranges before generating cached indices. By detecting overlaps in advance, the system can consolidate index generation efforts and avoid creating duplicate indices for overlapping ranges, reducing redundant processing while maintaining fast search capabilities.
Solution Approach 2:
The patent implements a feedback mechanism where the system checks whether a cached index already exists and corresponds to the range specified in a lookup function request. If a matching cached index is found, the system reuses it instead of generating a new one, thereby avoiding redundant processing and reducing index generation time.
Data Source
AI summary
In non-limiting examples of the present disclosure, systems, methods and devices for identifying a target value in a data set are presented. A request for a first target value in a data array may be received, wherein the request comprises first reference value. The data array may comprise a plurality of values and a plurality of value types included in one or more tables in a computer-implemented spreadsheet application. An index comprising a plurality of sorted reference numbers corresponding to a plurality of values in the data array may be generated based on the request. The index may be searched, and based at least on the search, the first reference value may be identified. In additional examples, the index may be cached and utilized to process additional target value requests that reference an overlapping range value with the first request.