 SOLVED

Highlighted

# 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

8 Replies
Highlighted

# Re: Using search function with array input to categorize rows

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

# Re: Using search function with array input to categorize rows

@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

Highlighted

# Re: Using search function with array input to categorize rows

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?
Highlighted

# Re: Using search function with array input to categorize rows

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

Highlighted
Solution

# Re: Using search function with array input to categorize rows

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.

Highlighted

# Re: Using search function with array input to categorize rows

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

=IFNA(LOOKUP(2,1/(
COUNTIF(B3,"*"&D\$3:D\$6&"*")),

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

# Re: Using search function with array input to categorize rows

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.
Highlighted

# Re: Using search function with array input to categorize rows

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