Forum Discussion
Solved
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 fo...
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.
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" ) |