Nov 20 2019 12:00 AM
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
Nov 20 2019 12:30 AM
@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))))
Nov 20 2019 01:45 AM - edited Nov 20 2019 01:59 AM
@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
Nov 20 2019 02:07 AM
Nov 20 2019 03:52 AM
@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)
Nov 20 2019 01:37 PM
SolutionPractically 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.
Nov 20 2019 11:13 PM
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:
Nov 21 2019 01:37 AM
Nov 21 2019 02:14 PM - edited Nov 21 2019 02:16 PM
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" ) |
Nov 20 2019 01:37 PM
SolutionPractically 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.