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.

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