1

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?

Improve this question
1
  • See my edit.................... Commented May 3, 2016 at 16:00

2 Answers 2

Reset to default
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:

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Can you explain why function with sumproduct works even though it uses same search formula which for me looks like culprit?
The Sumproduct by its very design forces the Search function to iterate through each cell in the range and returns all the rows in which those are true. because only one is true it returns the sum of one row. If you put b in A4 it would return 6. The Aggregate does the same but will return only the lowest row number.
As a programmer it is hard for me to grasp how external function (sumproduct) can force internal (search) to do anything differently. Also I have read documentation for both function and I did not find anything about such abilities of sumproduct function. Are there some other function that can force such behaviour? Where can I read more about this?
There are native Array formulas, like INDEX, SUMPRODUCT, AGGREGATE. These formulas work like CSE Array formulas without the need to "force" them to become array formulas. In these cases we have learned how to use their inherent abilities to do things that the original programmer did not intend, but work. As to where to learn, I have always use the need/find method. As I need something I find out where I can ask the right people. I don't know of documentation that delves into these side tracts, It has always been through this forum and others that I have learned.
0

you need an array formula:

enter image description here

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)
Improve this answer

7 Comments

Why do I need array formula? Match returns single value from whole array which is second parameter. Also for first version I do not need array formula even though I had an array. And last ctrl-shift-enter does not work in Excel Online.
@Trismegistos Then use the far simpler: =MATCH("b",A1:A4,0) see my edit
I need wildcards in A1 through A4 so MATCH wont work alone.
@Trismegistos So you are not trying to match "b", you are trying to match a cell Containing "b" somewhere in the cell ??
@Trismegistos, you need an array formula because the SEARCH function expects a value not an array, and unless you indicate an arrayformula the function takes only the first value of the range. (Try evaluating the formula and you'll see what it does). When you explicitly set the array {"a",...} Excel knows it's an array.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.