Get all values from a column based on criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-2173889%22%20slang%3D%22en-US%22%3EGet%20all%20values%20from%20a%20column%20based%20on%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2173889%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20table%20and%20I%20am%20trying%20to%20get%20ALL%20values%20(duplicated%20or%20not)%20from%20a%20column%20in%20a%20different%20sheet%20based%20on%20criteria.%20The%20criteria%20are%20the%20highlighted%20columns%20in%20the%20first%20image%20below.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20raw%20date%20looks%20like%20the%20other%20image.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20have%20attached%20the%20file%20in%20case%20it%20helps.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20before%20hand!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Jorglo_0-1614591618294.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F258921iC9721D56A6D82C7A%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Jorglo_0-1614591618294.png%22%20alt%3D%22Jorglo_0-1614591618294.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Jorglo_1-1614591834056.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F258930i1234CC06F438E137%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Jorglo_1-1614591834056.png%22%20alt%3D%22Jorglo_1-1614591834056.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2173889%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2173929%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20all%20values%20from%20a%20column%20based%20on%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2173929%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F981842%22%20target%3D%22_blank%22%3E%40Jorglo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DFILTER(INDEX(Table1%2C%20SEQUENCE(ROWS(Table1))%2C%20IF(%24E%243%3D%22Time%22%2C%20%7B1%2C2%7D%2C%20%7B1%2C3%7D))%2C%20COUNTIFS(%24C%243%2CTable1%5BCaseName%5D))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2173957%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20all%20values%20from%20a%20column%20based%20on%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2173957%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much!%20That%20works%20as%20desired.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20one%20little%20detail...%3C%2FP%3E%3CP%3EHow%20should%20I%20tweak%20the%20formula%20in%20order%20to%20only%20show%20the%20values%20and%20not%20repeat%20the%20case%20name%20to%20the%20left%20of%20each%20number%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Jorglo_0-1614593823429.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F258940iE710D5BDF366C3A7%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Jorglo_0-1614593823429.png%22%20alt%3D%22Jorglo_0-1614593823429.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again%20beforehand!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2174009%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20all%20values%20from%20a%20column%20based%20on%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2174009%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20what%20if%20I%20had%20many%20more%20columns%20in%20he%20raw%20data%20and%20not%20just%20two%3F%3C%2FP%3E%3CP%3ELike%20the%20image%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Jorglo_0-1614594948715.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F258945i780279D3F21D34AE%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Jorglo_0-1614594948715.png%22%20alt%3D%22Jorglo_0-1614594948715.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2174047%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20all%20values%20from%20a%20column%20based%20on%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2174047%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F981842%22%20target%3D%22_blank%22%3E%40Jorglo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20Office%20365%20there%20is%20a%20FILTER%20function%20which%20I%20think%20will%20do%20what%20you%20want.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20don't%20have%20365%20then%20see%20this%20youtube%20video%20which%20shows%20how%20to%20do%20this%20for%20older%20versions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DfDB1Ktyhp3Y%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DfDB1Ktyhp3Y%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eregards%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPeter%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2174059%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20all%20values%20from%20a%20column%20based%20on%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2174059%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F981842%22%20target%3D%22_blank%22%3E%40Jorglo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20list%20of%20columns%20to%20select%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20155px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F258958iBD7C05717BED04D0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eformula%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DFILTER(%0A%20%20INDEX(Table1%2C%0A%20%20%20%20%20%20%20%20SEQUENCE(ROWS(Table1))%2C%0A%20%20%20%20%20%20%20%20XMATCH(%20TRANSPOSE(%24E%243%3AINDEX(%24E%243%3A%24E%2410%2CCOUNTA(%24E%243%3A%24E%2410)))%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Table1%5B%23Headers%5D))%2C%0A%20%20COUNTIFS(%24C%243%2CTable1%5BCaseName%5D)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ERange%20E3%3AE10%20is%20taken%20with%20some%20gap%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.