How to use MATCH on a variable search range

[fcosta]

The following issue was due (mainly) to formula parameter settings, as explained by the first answer. It is left like that to help if others get stuck similarly.

I need to identify the cells containing the occurrences of a value (“TRUE”) in a column, say E.

With a simple MATCH function I find the first one. With that I calculate the row number of the cell with the first occurrence of “TRUE” in another cell, say M15.

Now I would like to go on searching on the rest of the E column after the cell of the first MATCH.

I try to use a formula like

=MATCH(“TRUE”, ADDRESS(M15, 5, 4, 1):E$100,0)

but I get the Err:502.
I tried to use INDIRECT and other functions (OFFSET, etc.) but I cannot find a way.

What am I doing wrong?

LOC.ods

[Lupp]

Please also consider this question and the answer/comments.

Usage of MATCH(): MATCH(Value; 1D_RangeReference; Mode) An address is a text, not a reference. You can create a reference from a correct address using INDIRECT().

The most flexible way to get variable range references is to use OFFSET().

The background of the question is repeating again and again. This may mainly be due to the fact that many users try to solve problems for which a database would be the proper application by spreadsheets. It is about something done with a SELECT WHERE statement in SQL.

Anyway you find a simplified demo of how to do such things reliably in spreadsheets here. For principal reasons solutions to SELECT WHERE by formulas cannot be efficient for large data collections. (Not to speak of ORDER BY.)

[fcosta]

Please also consider this question and the answer/comments.

Thank you very much!!! I had the feeling I was missing a trivial thing but I couldn’t figure out why. I would not have thought about this, since I do not remember having changed these setting myself in years.
Indeed now errors in formulas are more… predictable. This was major improvement.

The most flexible way to get variable range references is to use OFFSET().

Yep, fully agree, I reverted to this approach, that was not working previously for me for the same reason above. Thanks again.

Nonetheless I still have a problem applying the OFFSET within the MATCH, as it seems that the offset parameter is not applied.

I edit and attach a file in the original question, as I understand attachment to comments are not possible.

[fcosta]

I upladed an example file LOC.ods (hope it works).

Cell H5 has formula =IF(H4=""; MATCH(“SENT”; B5:B$100; 0); MATCH(“SENT”; OFFSET(B5:B$100;H4;0))) and correctly displays “7”

Similarly cell H^ has formula =IF(H5=""; MATCH(“SENT”; B5:B$100; 0); MATCH(“SENT”; OFFSET(B5:B$100;H5;0))) I would expect it to not display anything. In fact, the value of H5 is 7, so I understand the match should apply to the range B12:B$100 (B12 being B5+offset by 7) and should not match.

Is OFFSET using the formula in H5 (which is not numeric, so the offset is 0), instead of the value of the formula?
And if it is the case, is it possible to “force” OFFSET to use the value of the formula (I tried several functions FORMULA, VALUE, etc. but did not help)?

I guess I can make up a workaround with some intermediary cells, but maybe there is a “cleaner” way…