I have categorized certain ids. Now each id may belong to multiple categories. Let's say column A contains category names, column B has all ids that category contains. I add column C which has all ids ( a single id in a cell). Now i want to have in column D all category names which contain that id(from column C). IF that id is not found among categories(column A) then return "not found".
A B C D
category A a21|b43|g87 a21 category A
category B b43|j78|n99 b43 category A category B
c35 not found
I have tried using Index, Match, iferror, find etc but not able to achieve my intended objective. Any help will be really appreciated.