Librecalc: vlookup equivalent for unsorted data, closest key value

English
, , ,
#1

I have a spreadsheet with financial info. The data is unsorted (for example, not by the date when I sent a check, but in the order that it was processed) and the balance. I want to find out the approximate balance on a particular day, for example:

| jan 1 | $2000 |

| jan 3 | $5000 |

| jan 6 | $8000 |

| jan 4 | $4000 |

In this example, if I want to find the value for january 5, it would go through, find a value for january 5 or earlier, and return the balance on that date; in the example above, the value for jan 4. vlookup, index, match don’t quite seem to do what I want. Is there a way to do this?

#2

Are these cell contents fomatted numeric values or are they pure strings?

#3

The values are actual dates and values, formatted (not strings).

#6

You could use MAXIFS to find the date not greater than wanted, and then use exact VLOOKUP.

=VLOOKUP(MAXIFS(A1:A5;A1:A5;"<="&D1);A1:B5;2;0)

But generally I’d say that the original idea to do the “sorted range lookup” on unsorted data is bad. It would be more than twice longer (sorted range uses binary search, while searching over unsorted data would look through all the values, once for MAXIFS and once for VLOOKUP). It would be more difficult to debug problems in formulas, both because of added complexity of the formulas themselves, and because of not apparent correctness of the results (you would need to manually check all the data to make sure it’s (in)correct).

It’s better to simply sort the data range, and use normal sorted range lookup.

#4

Please upload a real sample file here with the real data order.

In my opinion you must combine some of the available functions, and must use helper cells to eliminate the too long formulas and nested IF()-s.

Try a simular function with NUMERIC data (where the range is A1:B5, and the date to search is D1):

=VLOOKUP(INDEX(A1:A5;MATCH(VLOOKUP(D1;A1:B5;1;1);A1:A5;1)+1);A1:B5;2;0)
#5

This approximate solution not handle the cases when the date EQUALS to one of dates of the table… (The formula will be longer and more complex… This is the reason why it is better to use helper cells for the parts of the applied formula.)

#7

If cell C1 contains the required date (in example jan 5), then the formula for balance can be like this:

=SUMIF($A$1:$A$4;"<="&C1;$B$1:$B$4)
#8

I doubt that summing the balances is what s/he’s looking for…

#9

I mean the sum of receipts and payments.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.