SOLVED

Using search function with array input to categorize rows

Copper Contributor

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

8 Replies

@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))))

@Jan Karel Pieterse : 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?

@Jan Karel Pieterse : Understanding bits, but not the whole puzzle :) 

Why do you minus the row of D2? And what does the first ROW-statement actually do ROW(D3:D6)

best response confirmed by tenorway (Copper Contributor)
Solution

@tenorway 

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.

@tenorway 

In the attached file, the wildcard lookup formula in C3 is: 

=IFNA(LOOKUP(2,1/(
COUNTIF(B3,"*"&D$3:D$6&"*")),
E$3:E$6),"Keyword Not Found")

When copied down rows, the foregoing formula returns results, as shown below: 

Wildcard Lookup.PNG

ROW(D3:D6) returns the row number IF there is a match. But since I am using the INDEX function, I need the numbers to start at 1, hence the subtraction by the row immediately above the D3:D6. This ensures the formula keeps working if you insert rows above row 2.

@tenorway 

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" )
1 best response

Accepted Solutions
best response confirmed by tenorway (Copper Contributor)
Solution

@tenorway 

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.

View solution in original post