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