Forum Discussion
Using search function with array input to categorize rows
Hi !
I am trying to use a combination of search, isnumber, match and index to categorize rows.
The category is supposed to be displayed on the same row.
I have seen the combination used for the exact same purpose that I need it for, but it doesn't work.
I have broken up the formulas, and it seems the search function doesn't return array result as I would expect. The match function needs an array, and fails. At least that's what seems to be the problem.
I have used Ctrl+Shift+Enter, and the braces are present around the formula.
I would be super happy if you could assist me in solving this puzzle.
Attached is a sample sheet. The formula is insertes as text, since it doesn't "compile".
Both in norwegian and english version.
By the way, I am using Office 365
Practically the same that Jan Karel suggested
=IFNA(INDEX($E$3:$E$6,MATCH(1,INDEX(--ISNUMBER(SEARCH($D$3:$D$6,B3)),0),0)),"")Perhaps that's non-array formula.
8 Replies
The match index appear to work fine but then, so does everything else. I also included a few formulas to return multiple matches, either as a dynamic array or as a text string
1 = IFNA( LOOKUP( 2, 1 / ( COUNTIF(@text,"*"&keyword&"*") ),category ), "Not Found" ) 2 = FILTER( category, COUNTIF(@text,"*"&keyword&"*" ), "Not found" ) = overflow from previous 3 = FILTER( category, ISNUMBER( SEARCH( keyword, @text ) ), "Not found" ) = overflow from previous 4 = TEXTJOIN( "/", 1, IF( COUNTIF(@text,"*"&keyword&"*" ), category, "" ) ) 5 = TEXTJOIN( "/", 1, IF( ISNUMBER( SEARCH( keyword, @text ) ), category, "" ) ) 6 = IFERROR( INDEX( category, MATCH( TRUE, ISNUMBER( SEARCH( keyword, @text ) ), 0 ) ), " Not found" ) 7 = XLOOKUP( TRUE, ISNUMBER( SEARCH( keyword, @text ) ), category, "Not found" ) tenorway This is one way of doing it (array formula):
=INDEX($E$3:$E$6,MAX(IF(ISNA(MATCH("*"&$D$3:$D$6&"*",B3,0)),-1,ROW($D$3:$D$6)-ROW($D$2))))JKPieterse : Thank's for the response 🙂
It works out perfectly 🙂
Do you know why my approach using search didn't work? I don't like not understanding why I failed.
It would also be very nice if you could explain how your formula does your magic? Especially the use of ROW and D2
Regards
- My formula tries to match all four keywords in E3:E6 to the text in column B using a wildcard around the four keywords so they are matched when they exist anywhere in the text. If a match is found the formula returns the found row, minus the row of E2. If not found, it returns -1. The max function is there to just return the location of the found keyword within the list of keywords, ignoring the -1's in the array.
Clear as mud?
