Excel match function not properly treating array arguments

Question

I have created following query

=MATCH(TRUE, ISNUMBER(SEARCH({"a","b","c","d"}, "b")), 0)

it returns 2 because b is second argument in array {"a","b","c","d"} but when I replace hardcoded array with data range i.e.

=MATCH(TRUE, ISNUMBER(SEARCH(A1:A4, "b")), 0)

query returns #N/A even thought A1 contains a, A2 contains b, A3 contains c and A4 contains d.

Why it is not working and how to fix it? I am using search function because I am searching for "fuzzy matches" possibly with wildcards not exact ones (then I would use vlookup).

I am using excel 365 online.

Edit: After a bit of experimentation it turned out that

=SEARCH(A1:A2, "a")

returns #Value! even though

=SEARCH({"a", "b"}, "a")

returns 1. How can I fix it to get result I want?

Answer 1

The office app online only allows the most basic of formulas, so no CSE Array formulas.

As to why one version of your formula requires the Ctrl-Shift-Enter and the other does not, is the design of the function. Since most of us did not design the SEARCH function it is hard to answer why.

There are array function that do not require Ctrl-Shift-Enter.

For the online version you will want to use the Aggregate() function:

=AGGREGATE(15,6,ROW(1:4)/ (ISNUMBER(SEARCH(A1:A4, "b"))), 1)

Or you could use SUMPRODUCT():

=SUMPRODUCT((ROW(A1:A4))*(ISNUMBER(SEARCH(A1:A4,"b"))))

This will return the row:

Answer 2

you need an array formula:

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

Another approach is to use:

=MATCH("b",A1:A4,0)