Use array formula for range using search

[hiker100]

I have this formula that uses one search function to do multiple searches.

The formula is

Please Login or Register  to view this content.

but I'm getting an error message.

The answer I want is 2 (row 2) since all the matches occur there.

What is wrong with the formula?

[hiker100]

All the words in each row are contained in column B.

[hiker100]

I should have mentioned that I have Excel 2010, with upgrading to Excel 2013 soon.

Maybe using a "match(true" statement would work but then I wouldn't get multiple matches.

There doesn't seem to be a way to do this in Excel.

[humdingaling]

one search to do multiple search...whats the outcome if multiple are found?
also a sample file would make it easier to test outcomes for you

[AlKey]

Maybe something like this

in B1 and copy down

=IFERROR(IF(LOOKUP(2^15,SEARCH({"house","tent","zebra"},B1)),ROW()),"")

Data Range

	B	C
1	dog cat zebra	1
2	zebra house	2
3	car mouse
4	box can orange tent	4

[FDibbins]

AlKey, I think the search needs to find the cell that matches all of them

[AlKey]

AlKey, I think the search needs to find the cell that matches all of them

I don't think I know exactly what OP wants. but here is something different


=SUM(COUNTIF(B1,"*"&{"house","tent","zebra"}&"*"))


Data Range

	B	C
1	dog cat zebra	1
2	zebra house	2
3	car mouse	0
4	box can orange tent	1

[hiker100]

Thanks for getting back to me. I would like to keep this part of the formula intact as I can change the search criteria easily.

Please Login or Register  to view this content.

As an illustration, If I wanted to change my match to "house" and "zebra" only; I would change =7 to =5 (1 and 4 added)

In my original example I want the formula to search the range B1:B4 to give me the row number that it matches to, which is row 2 from my example.

[hiker100]

I'll attach a file. Right now the formula works if you do it by cell. It shows a TRUE for C2. Instead of copying down the formula, I would like it to find the row numbers for the rows with TRUE without creating a column C.
I would copy down this formula down if there are multiple matches. I would probably use the small function to find multiple matches. So "small(if....rows(A1:A1)"

It is trickier than I thought.

[XOR LX]

Hi.

So what are your expected results for the attached, and in which cell(s) should these results be placed?

Regards

[hiker100]

The formula can be placed in C1.
My expected result is 2 since that is where the criteria matches.
There could be multiple matches so a small(if or other similar function would need to be combined with the formula in the spreadsheet.

[hiker100]

I'm wondering if the sumproduct is causing the issue in that it can't work with arrays. Instead of sumproduct maybe use sum(if.

[hiker100]

l've used the search function in arrays before but not sumproduct. Could I use the Aggregate function in place of the sumproduct. It probably work faster as well.