MATCH backwards and look in formatted values

[dandv]

I have a sheet of expenses like this, with a column of numbers, some of which represent Balances and are formatted as "Balance: $"0.00. In that linked sheet, I16 is an example of a balance.

In a cell the bottom of the shee, I want to find the value in the most recent (i.e. first above) “Balance” call from that column I. How can I do that in LibreCalc?

MATCH doesn’t support searching backwards, and it doesn’t support searching within the formatted value either. =MATCH("Balance.*", I1:I100, 0) won’t find that I16 cell, and =FIND("Balance", I16) also returns #VALUE!.

[anon73440385]

Any =MATCH("Balance.*",..) won’t work at all, since the word “Balance” is not part of the content of the cell. It is part of the formatting, but the cell content is 500 (formatting a cell doesn’t change its content but the visual presentation of its content). Same applies for using Find(). For illustration purposes check with =MATCH(500;I1:I100;0) (results in 16) and you’ll see what I mean. So if you want to MATCH() for “Balance.” you would need "Balance" becoming part of the cell content (for me it is not clear where you get the 500 from) and using something like ="Balance " & B24 & "$" in cell I16 and assuming 500 is from B24 (which is what I won’t recommend and just mentioned here to clarify the issue).

[dandv]

@anon73440385: 500 is entered by the user, e.g. from a financial institution, then the user applies a format that results in, e.g. Checking balance: 500, or IRA balance: 1000. It’s important to keep numbers in those cells, to do arithmetic later. The problem is I want to search for cells whose formatted output, i.e. what the user sees, contains Balance. Is there no way of doing that?

[anon73440385]

As explained - there is no way to find what is not part of the cell (not talking about user code, i.e macros).

[erAck]

I want to find the value in the most recent (i.e. first above) “Balance” call from that column I.

I’m taking that as “the last non-empty cell above I16 (the Balance)” of your example, so that’s

=LOOKUP(2;1/(NOT(ISBLANK(I1:I15)));I1:I15)

Short explanation:

The LOOKUP() creates an array of TRUE and FALSE (1 and 0) values of the NOT(ISBLANK(…)) expression, which then serves as the divisor of 1/…, producing an array of 1 and #DIV/0! error values, of which the errors are effectively ignored in this mode so the lookup of the value 2 in this array yields the position of the last 1, which is used to obtain the value.

If you want to be able to insert rows immediately before row 16, which wouldn’t adjust the I1:I15 range reference without activating the “expand references” setting, then use

=LOOKUP(2;1/(NOT(ISBLANK(I1:OFFSET(I16;-1;0))));I1:OFFSET(I16;-1;0))