New Contributor

# Get all values from a column based on criteria

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!

7 Replies

# Re: Get all values from a column based on criteria

That could be

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

# Re: Get all values from a column based on criteria

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!

# Re: Get all values from a column based on criteria

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

Like the image below.

# Re: Get all values from a column based on criteria

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...

# Re: Get all values from a column based on criteria

If you have list of columns to select like this

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

# Re: Get all values from a column based on criteria

That works great, thanks very much.

# Re: Get all values from a column based on criteria

@Jorglo , you are welcome, glad it helped.