I have this formula that uses one search function to do multiple searches.
The formula is
Formula:
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.HTML Code:
What is wrong with the formula?
All the words in each row are contained in column B.
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.
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
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Maybe something like this
in B1 and copy down
=IFERROR(IF(LOOKUP(2^15,SEARCH({"house","tent","zebra"},B1)),ROW()),"")
Data Range
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
AlKey, I think the search needs to find the cell that matches all of them
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I don't think I know exactly what OP wants. but here is something differentOriginally Posted by FDibbins
=SUM(COUNTIF(B1,"*"&{"house","tent","zebra"}&"*"))
Data Range
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.
Formula: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.
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.
Hi.
So what are your expected results for the attached, and in which cell(s) should these results be placed?
Regards
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.
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.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks