Forum Discussion
Formula for returning words that fall under a specific class
Dear,
I am in search of a formula that can help me achieve the following:
I have 2 colums, the first one contains classes, the second one contains items. My goal is to give all the items that fall under a specific class. See the example to make it more clear.
I don't know if this is possible?
Would be very helpfull!!
Thanks in advance,
Andreas
5 Replies
Hi Andreas,
That could be as array formula (enter it by Ctrl+Shift+Enter)
=IFERROR(INDEX(DATA!$C$6:$C$14, SMALL(IF($B$2=DATA!$B$6:$B$14, ROW(DATA!$C$6:$C$14)-ROW($B$5),""), ROW()-ROW($B$5))),"")
How it works see, for example, https://www.ablebits.com/office-addins-blog/2017/02/22/vlookup-multiple-values-excel/
Andreas,
=INDEX(DATA!C:C,AGGREGATE(15,6,ROW(DATA!$C$6:$C$14)/(DATA!$B$6:$B$14=$B$2),ROWS(B$6:B6)))
Does this produce unique answers?
If for example an items appears twice in the same class, would it then be mentioned twice in the result?
It would be ideal if the word would only be mentioned once.
Thanks a lot already for the formula you gave!
Andreas
Andreas Schryvers wrote:
Does this produce unique answers?
It would be ideal if the word would only be mentioned once.
Well, why didn't say so in your in first posting?
{=INDEX(DATA!C:C,AGGREGATE(15,6,ROW(DATA!$B$6:$B$15)/(IFERROR(MATCH($B$2&DATA!$C$6:$C$15,DATA!$B$6:$B$15&DATA!$C$6:$C$15,0),FALSE)=ROW(DATA!$B$6:$B$15)-5),ROWS(B$6:B6)))}
Yes, even better - forgot about small in aggregate every time
