Get all values from a column based on criteria

New Contributor

Hi,

 

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!

 

Jorglo_0-1614591618294.png

Jorglo_1-1614591834056.png

 

7 Replies

@Jorglo 

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?

 

Jorglo_0-1614593823429.png

 

 

 

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.

 

Jorglo_0-1614594948715.png

 

@Jorglo 

 

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.

 

https://www.youtube.com/watch?v=fDB1Ktyhp3Y

 

Hope this helps!

 

regards,

 

Peter

 

Check the Excel Essentials Course: https://courses.xelplus.com/p/learn-excel-essentialsQuickly learn how to lookup one value in Excel and return multiple mat...

@Jorglo 

If you have list of columns to select like this

image.png

formula could be

=FILTER(
  INDEX(Table1,
        SEQUENCE(ROWS(Table1)),
        XMATCH( TRANSPOSE($E$3:INDEX($E$3:$E$10,COUNTA($E$3:$E$10))),
                Table1[#Headers])),
  COUNTIFS($C$3,Table1[CaseName])
)

Range E3:E10 is taken with some gap

@Sergei Baklan 

 

That works great, thanks very much.

@Jorglo , you are welcome, glad it helped.