Search for one value in column A and list all belonging values from column B

[Beatrix und Andreas]

Hi,

I have an excel sheet with 3 columns. In column A there are categories, in column B there are the different devices from each category. In column C I want to list all diveces depending of a condition. Please have a look to examble excel sheet. I know that you can solf this with INDEX, VERGLEICH and ARRAY but I don't know how.

Thanks for your help.

Andreas

[#2]

Andreas,

first delete the rows with the sub headers and fill in a column header for column A.

Then Press CTRL-SHIFT-L and filter in column A.

[#3]

Hi Detlev,

but I need the subheaders, because I want too hide column A when the sheet was finished.

And how is the formula for column C?

Thanks

[#4]

Then try this in C3:

=INDEX(B:B,AGGREGATE(15,6,ROW($A$3:$A$50)/($A$3:$A$50="Monitore"),ROWS(C$3:C3)))

[#5]

I inserted the following formula (I hope it is correct for te german version), but I got the error #DIV/0!

=INDEX(B:B;SUMME(15;6;ZEILE($A$3:$A$50)/($A$3:$A$50="Monitore");ZEILE(C$3:C3)))

[#6]

In German that would be:

=INDEX(B:B;AGGREGAT(15;6;ZEILE($A$3:$A$50)/($A$3:$A$50="Monitore");ZEILEN(C$3:C3)))

[#7]

Hi Detlev,

Have many thanks. It works nearly perfect ;)

If I copied the formula for example to 20 rows, but in column B there are only 10results, then I get an error message from row eleven to twenty  #ZAHL!

I can create a conditional formatting that #ZAHL! should printed white but exists there a better way?

Greetings Andreas

[#8]

Alternatively you can wrap IFERROR() around your formula.

[#9 · Accepted solution]

Hi Detlev,

brilliant :) Hav emany thanks. For my examble this is the perfect working formula:

=WENNFEHLER(INDEX(L:L;AGGREGAT(15;6;ZEILE($K$3:$K$300)/($K$3:$K$300=$H$4);ZEILEN(K$3:K16)));"")

This was my first question in such a community and it worked perfect. Your answers were very quick and they led to the goal. Have a nice weekend. Greetings Andreas

[#10]

Thanks, Andreas. You're welcome.

If you prefer German speaking/writing support then there are several communities/forums you can join.