Organize Binary Search LibreOffice Calc?

Question

Does anybody know of a way to organize a binary search in LibreOffice Calc?

To take a simple example, say I have column A with the numbers 1-16 listed, sorted in ascending order: (1,2,3,...); and column B which lists names of widgets; and I want to find a given widget by searching the numbers in column A. Simple enough: =SEARCH(number;A1:A16;B1:B16). But suppose my list contains 1600 numbers, sorted, and I need the widget name associated with entry 992. 'Search' would have to go through 992 iterations to reach the desired value. A binary search, a la java, would cut that down to maybe 10 iterations. But can that be done?

As nearly as I can tell, each cell in a spreadsheet is fundamentally independent of all the others, and even organizing a binary search "tree" within a group of cells, Calc would want to go through every cell and calculate its value based on whatever formula the given cell contains - even if the cell is extraneous to the search thread.

SO: is a binary search possible?

I have built a binary search tree inside a LibreCalc sheet. Formally, the tree works: visually, I get something like:

binary search tree

where (*) represents cell contents that show the presence of 'hits'. However, this is not really a binary search, because LibreOffice Calc will elaborate all cells - say A, A1, A2 - even though they are not on the search path, which passes through (B)-(B1).

The [**SEARCH() function**](https://help.libreoffice.org/latest/en-US/text/scalc/01/04060110.html?DbPAR=CALC&HID=SC_HID_FUNC_SUCHEN#bm_id3149484) doesn't solve the described problem. To search for values in a table, use the [**VLOOKUP()**](https://help.libreoffice.org/latest/en-US/text/scalc/01/04060109.html) function or a combination of [**INDEX(MATCH()) functions**](https://www.google.com/search?q=vlookup+vs+index+match). The correctly specified last parameter in VLOOKUP() or MATCH() just turns on accelerated search - ***Sorted columns can be searched much faster*** (see Help) — JohnSUN, Apr 18 '23 at 06:53
Hi, John. Yes, I know that SEARCh won't get me there - hence the post :P Eventually I came across some articles explaining that VLOOPUP implements a binary search in 'approximate' mode - which I guess is what you were trying to tell me, so THANKS. Fundamentally, however, I was originally asking whether it was possible to build a cell-based branching algorithm *within* a spreadsheet that would execute a binary search. Sorry for the confusion. — Mychal Simonian, Apr 28 '23 at 18:10

score 0 · Answer 1 · edited Apr 28 '23 at 19:55

There is a fundamental reason for asking fundamental questions: understanding what we do. Yes, there are a hundred articles and posts out there telling the user how to use LOOKUP, VLOOKUP, XLOOKUP (such as: https://exceljet.net/functions/vlookup-function)... but how these work can have a major impact on the work that gets done. A 5% gain in performance might not seem to be much to somebody sorting 1000 entries, but try sorting 100K entries and see what happens.

My question was answered here:

It turns out that Excel, Libreoffice Calc, etc., implement a binary search algorithm within a number of 'functions', including LOOKUP, VLOOKUP, XLOOKUP. Thus, my spreadsheet, as constructed (with VLOOKUP set to approximate), already implements a binary search.

Now, as for implementing a binary search using formulas inserted within cells in Calc:

cell-based branched search

This is a part of a tree I built. J23 contains the data point to evaluate. The evaluation is sine values between 0,1 and 89,9. There will be a cascading dynamic through the tree, with all cells in the tree acquiring one of two values: '1' or '2'. This differs from a binary search, where only a portion of the tree is iterated through, with half the remaining tree being eliminated at each iteration.

Further, a separate, analytical component is necessary to evaluate the end state of this cell-based tree. The analytical component will need to iterate through the final level of the tree in search of the flag that indicates the cell containing the desired datum. A true binary search would have no need of this.

Organize Binary Search LibreOffice Calc?

1 Answers1