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.
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))))
JKPieterse : 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?JKPieterse : 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)
- 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.