Empty cell in Google Sheets not treated as empty by MATCH function

Question

I am trying to use the =MATCH("zzzz";$A2:2) function to find the column containing the last non-empty cell in row 2 of my worksheet (containing text values).

The value returned (4) implies that the cell D2 is non-empty, even though it appears to be so, and the EQ function says its content is equal to that of the truly empty neighboring cell C2.

If I copy C2 onto D2, or if I select D2 and choose "delete value" from the menu, the problem is fixed. The correct behavior is displayed in rows 5 and 6.

Actually the problem originally occurs in a much larger worksheet, and I cannot afford to fix all "pseudo-empty" cells manually. One first step would be to figure out what is the hidden content of cell D2, which is considered empty by the EQ function but not by the MATCH function.

Is there a simple way to do that?

Answer 1

The formula is missing the search_type parameter, which makes the match() function use its default and assume that the data is sorted. This means that it uses a binary search, which will not work with unsorted data such as the data in the sample spreadsheet. This explains the unexpected result.

To make it work, add a search_type parameter of 0 and sort the data in reverse order by column number, or preferably apply this pattern:

=xmatch("?*"; arrayformula(to_text(A2:2)); 2; -1)

The formula must go to a row other than row 2.

See match() and xmatch().