formula to get list based on condition

[jaryszek]

Hi Guys,

i have source data like here:
Screenshot_24.png

and want to get result like here:

Screenshot_25.png

So in this case in column A you can see :
Windows 1709
Windows 1809

because user in souirce worksheet have choosen App 2.0 SPS3.

And one more difficulty here -> i have to have on the top of created list manually inputted Text.

How to create formula to get list.

Please help,
Jacek

[AliGW]

Try this in A2 copied down:

=IFERROR(INDEX(source!$B$1:$B$500,AGGREGATE(15,6,ROW($2:$500)/(source!$A$1:$A$500=source!$E$2),ROW(1:1))),"")

[jomaor1]

Hi,

Try this on C2, and drag down:

Please Login or Register  to view this content.

and Ctrl+Shift+Enter (It's an Array Formula)

Cheers!

[jaryszek]

Thank you !!!

I prefer not to use aggregate function - i am afraid of performance from it.

Best,
Jacek

[AliGW]

Well, it's become popular precisely because it performs better than an array-entered formula!

But it's up to you - you could have specified that you wanted a solution that did not use AGGREGATE.

[jaryszek]

Hi Ali,

o wow thank you. So you say that aggregate is better than array formula ?

Please Login or Register  to view this content.

Thanks. But i am always looking for the best solution - best and fastest.

Best,
Jacek