SOLVED
Home

Using search function with array input to categorize rows

%3CLINGO-SUB%20id%3D%22lingo-sub-1019725%22%20slang%3D%22en-US%22%3EUsing%20search%20function%20with%20array%20input%20to%20categorize%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1019725%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20use%20a%20combination%20of%20search%2C%20isnumber%2C%20match%20and%20index%20to%20categorize%20rows.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20category%20is%20supposed%20to%20be%20displayed%20on%20the%20same%20row.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20seen%20the%20combination%20used%20for%20the%20exact%20same%20purpose%20that%20I%20need%20it%20for%2C%20but%20it%20doesn't%20work.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20broken%20up%20the%20formulas%2C%20and%20it%20seems%20the%20search%20function%20doesn't%20return%20array%20result%20as%20I%20would%20expect.%20The%20match%20function%20needs%20an%20array%2C%20and%20fails.%20At%20least%20that's%20what%20seems%20to%20be%20the%20problem.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20used%20Ctrl%2BShift%2BEnter%2C%20and%20the%20braces%20are%20present%20around%20the%20formula.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20super%20happy%20if%20you%20could%20assist%20me%20in%20solving%20this%20puzzle.%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20is%20a%20sample%20sheet.%20The%20formula%20is%20insertes%20as%20text%2C%20since%20it%20doesn't%20%22compile%22.%26nbsp%3B%3C%2FP%3E%3CP%3EBoth%20in%20norwegian%20and%20english%20version.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBy%20the%20way%2C%20I%20am%20using%20Office%20365%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1019725%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Formulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1019804%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20search%20function%20with%20array%20input%20to%20categorize%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1019804%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F462818%22%20target%3D%22_blank%22%3E%40tenorway%3C%2FA%3E%20This%20is%20one%20way%20of%20doing%20it%20(array%20formula)%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDEX(%24E%243%3A%24E%246%2CMAX(IF(ISNA(MATCH(%22*%22%26amp%3B%24D%243%3A%24D%246%26amp%3B%22*%22%2CB3%2C0))%2C-1%2CROW(%24D%243%3A%24D%246)-ROW(%24D%242))))%0A%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1019943%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20search%20function%20with%20array%20input%20to%20categorize%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1019943%22%20slang%3D%22en-US%22%3E%3CP%3EAnd%2C%20could%20you%20also%20explain%20how%20your%20formulas%20actually%20does%20their%20magic%3F%20I%20would%20really%20like%20to%20understand%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1019929%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20search%20function%20with%20array%20input%20to%20categorize%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1019929%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3A%20Thank's%20for%20the%20response%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20works%20out%20perfectly%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20know%20why%20my%20approach%20using%20search%20didn't%20work%3F%20I%20don't%20like%20not%20understanding%20why%20I%20failed.%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20would%20also%20be%20very%20nice%20if%20you%20could%20explain%20how%20your%20formula%20does%20your%20magic%3F%20Especially%20the%20use%20of%20ROW%20and%20D2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1019948%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20search%20function%20with%20array%20input%20to%20categorize%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1019948%22%20slang%3D%22en-US%22%3EMy%20formula%20tries%20to%20match%20all%20four%20keywords%20in%20E3%3AE6%20to%20the%20text%20in%20column%20B%20using%20a%20wildcard%20around%20the%20four%20keywords%20so%20they%20are%20matched%20when%20they%20exist%20anywhere%20in%20the%20text.%20If%20a%20match%20is%20found%20the%20formula%20returns%20the%20found%20row%2C%20minus%20the%20row%20of%20E2.%20If%20not%20found%2C%20it%20returns%20-1.%20The%20max%20function%20is%20there%20to%20just%20return%20the%20location%20of%20the%20found%20keyword%20within%20the%20list%20of%20keywords%2C%20ignoring%20the%20-1's%20in%20the%20array.%3CBR%20%2F%3EClear%20as%20mud%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1020104%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20search%20function%20with%20array%20input%20to%20categorize%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1020104%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3A%20Understanding%20bits%2C%20but%20not%20the%20whole%20puzzle%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%20do%20you%20minus%20the%20row%20of%20D2%3F%20And%20what%20does%20the%20first%20ROW-statement%20actually%20do%20ROW(D3%3AD6)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1022890%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20search%20function%20with%20array%20input%20to%20categorize%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1022890%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F462818%22%20target%3D%22_blank%22%3E%40tenorway%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPractically%20the%20same%20that%20Jan%20Karel%20suggested%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFNA(INDEX(%24E%243%3A%24E%246%2CMATCH(1%2CINDEX(--ISNUMBER(SEARCH(%24D%243%3A%24D%246%2CB3))%2C0)%2C0))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EPerhaps%20that's%20non-array%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1023526%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20search%20function%20with%20array%20input%20to%20categorize%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1023526%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F462818%22%20target%3D%22_blank%22%3E%40tenorway%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20file%2C%20the%20wildcard%20lookup%20formula%20in%20C3%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIFNA(LOOKUP(2%2C1%2F(%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ECOUNTIF(B3%2C%22*%22%26amp%3BD%243%3AD%246%26amp%3B%22*%22))%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EE%243%3AE%246)%2C%22Keyword%20Not%20Found%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EWhen%20copied%20down%20rows%2C%20the%20foregoing%20formula%20returns%20results%2C%20as%20shown%20below%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20533px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F158308i54010DA6587B5FE0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Wildcard%20Lookup.PNG%22%20title%3D%22Wildcard%20Lookup.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1023674%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20search%20function%20with%20array%20input%20to%20categorize%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1023674%22%20slang%3D%22en-US%22%3EROW(D3%3AD6)%20returns%20the%20row%20number%20IF%20there%20is%20a%20match.%20But%20since%20I%20am%20using%20the%20INDEX%20function%2C%20I%20need%20the%20numbers%20to%20start%20at%201%2C%20hence%20the%20subtraction%20by%20the%20row%20immediately%20above%20the%20D3%3AD6.%20This%20ensures%20the%20formula%20keeps%20working%20if%20you%20insert%20rows%20above%20row%202.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1025067%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20search%20function%20with%20array%20input%20to%20categorize%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1025067%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F462818%22%20target%3D%22_blank%22%3E%40tenorway%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20match%20index%20appear%20to%20work%20fine%20but%20then%2C%20so%20does%20everything%20else.%26nbsp%3B%20I%20also%20included%20a%20few%20formulas%20to%20return%20multiple%20matches%2C%20either%20as%20a%20dynamic%20array%20or%20as%20a%20text%20string%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%3D%20IFNA(%20LOOKUP(%202%2C%201%20%2F%20(%20COUNTIF(%40text%2C%22*%22%26amp%3Bkeyword%26amp%3B%22*%22)%20)%2Ccategory%20)%2C%20%22Not%20Found%22%20)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E%3D%20FILTER(%20category%2C%20COUNTIF(%40text%2C%22*%22%26amp%3Bkeyword%26amp%3B%22*%22%20)%2C%20%22Not%20found%22%20)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3D%20overflow%20from%20previous%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E%3D%20FILTER(%20category%2C%20ISNUMBER(%20SEARCH(%20keyword%2C%20%40text%20)%20)%2C%20%22Not%20found%22%20)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3D%20overflow%20from%20previous%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E%3D%20TEXTJOIN(%20%22%2F%22%2C%201%2C%20IF(%20COUNTIF(%40text%2C%22*%22%26amp%3Bkeyword%26amp%3B%22*%22%20)%2C%20category%2C%20%22%22%20)%20)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E%3D%20TEXTJOIN(%20%22%2F%22%2C%201%2C%20IF(%20ISNUMBER(%20SEARCH(%20keyword%2C%20%40text%20)%20)%2C%20category%2C%20%22%22%20)%20)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E%3D%20IFERROR(%20INDEX(%20category%2C%20MATCH(%20TRUE%2C%20ISNUMBER(%20SEARCH(%20keyword%2C%20%40text%20)%20)%2C%200%20)%20)%2C%20%22%20Not%20found%22%20)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3E%3D%20XLOOKUP(%20TRUE%2C%20ISNUMBER(%20SEARCH(%20keyword%2C%20%40text%20)%20)%2C%20category%2C%20%22Not%20found%22%20)%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E
Highlighted
tenorway
New 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)

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" )
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies