Get all values from a column based on criteria

New Contributor



I have a table and I am trying to get ALL values (duplicated or not) from a column in a different sheet based on criteria. The criteria are the highlighted columns in the first image below. 

The raw date looks like the other image.


I also have attached the file in case it helps.


Many thanks before hand!





7 Replies


That could be

=FILTER(INDEX(Table1, SEQUENCE(ROWS(Table1)), IF($E$3="Time", {1,2}, {1,3})), COUNTIFS($C$3,Table1[CaseName]))


@Sergei Baklan 


Thank you very much! That works as desired.


Just one little detail...

How should I tweak the formula in order to only show the values and not repeat the case name to the left of each number?






Thanks again beforehand!

@Sergei Baklan 


Also, what if I had many more columns in he raw data and not just two?

Like the image below.






If you have Office 365 there is a FILTER function which I think will do what you want.


If you don't have 365 then see this youtube video which shows how to do this for older versions.


Hope this helps!






Check the Excel Essentials Course: learn how to lookup one value in Excel and return multiple mat...


If you have list of columns to select like this


formula could be

        XMATCH( TRANSPOSE($E$3:INDEX($E$3:$E$10,COUNTA($E$3:$E$10))),

Range E3:E10 is taken with some gap

@Sergei Baklan 


That works great, thanks very much.

@Jorglo , you are welcome, glad it helped.