SOLVED

Excel FILTER built-in function

%3CLINGO-SUB%20id%3D%22lingo-sub-2334472%22%20slang%3D%22en-US%22%3EExcel%20FILTER%20built-in%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2334472%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20but%20didn't%20succed%20to%20use%20FILTER%20and%20OFFSET%20functions%20in%20the%20same%20formula%20in%20order%20to%20get%20values%20located%20in%20the%202nd%20column%20of%20a%20table%2C%20only%20for%20rows%20matching%20a%20criteria.%20It%20looks%20like%20OFFSET%20isn't%20able%20to%20use%20FILTER%20result%20as%20its%20first%20argument.%20Am%20I%20right%3F%20Is%20there%20any%20other%20solution%20to%20what%20I%20want%20to%20acheive%20(without%20using%20VBA%2C%20as%20I'd%20like%20to%20use%20this%20formula%20for%20validation%20purpose)%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20your%20replies%2C%3C%2FP%3E%3CP%3EEric%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2334472%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2334500%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20FILTER%20built-in%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2334500%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047665%22%20target%3D%22_blank%22%3E%40ericGuyaderBerger%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20you%20mean%3C%2FP%3E%0A%3CP%3E%3DFILTER(Table%5BSecondColumn%5D%2C%20criteria)%3C%2FP%3E%0A%3CP%3E%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2334553%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20FILTER%20built-in%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2334553%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047665%22%20target%3D%22_blank%22%3E%40ericGuyaderBerger%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThus%20formula%20is%20literally%20as%20above%2C%20use%20second%20column%20as%20first%20parameter%20and%20formula%20for%20criteria%20on%20first%20column%20as%20second%20parameter.%3C%2FP%3E%0A%3CP%3EBy%20the%20way%2C%20I%20try%20to%20play%20with%20Analyze%20Data%20on%20such%20table%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%20147px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F279016i749EBFF643B2401B%2Fimage-size%2Flarge%3Fv%3Dv2%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%3Easking%3C%2FP%3E%0A%3CP%3E%3CEM%3E%3CSTRONG%3Efilter%20'Two'%20where%20'One'%20is%20a1%20as%20table%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3EResult%20is%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%20182px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F279033iEC485DEFD41FFC54%2Fimage-size%2Flarge%3Fv%3Dv2%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%3Ewith%20generated%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(SUM(ISERROR(Table1)*1%2CMIN(SUM(1-ISNUMBER(Table1%5BTwo%5D))%2CSUM(1-ISTEXT(Table1%5BTwo%5D))))%26gt%3B0%2CNA()%2CSORT(UNIQUE(FILTER(Table1%5BTwo%5D%2CTable1%5BOne%5D%3D%22a1%22))))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EBit%20overcomplicated%2C%20but%20that's%20robot.%20We%20may%20skip%20error%20handling%20and%20take%20only%20FILTER%20part.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi,

 

I tried but didn't succed to use FILTER and OFFSET functions in the same formula in order to get values located in the 2nd column of a table, only for rows matching a criteria. It looks like OFFSET isn't able to use FILTER result as its first argument. Am I right? Is there any other solution to what I want to acheive (without using VBA, as I'd like to use this formula for validation purpose)?

 

Thanks in advance for your replies,

Eric  

6 Replies
best response confirmed by ericGuyaderBerger (Occasional Contributor)
Solution

@ericGuyaderBerger 

Do you mean

=FILTER(Table[SecondColumn], criteria)

?

Thank you, this solution takes me a step further, as I can get the only values of my second column matching the criteria based on first column values.
But if it works as a cell's formula, it still doesn't work as a validation formula.

@ericGuyaderBerger 

Thus formula is literally as above, use second column as first parameter and formula for criteria on first column as second parameter.

By the way, I try to play with Analyze Data on such table

image.png

asking

filter 'Two' where 'One' is a1 as table

Result is

image.png

with generated formula

=IF(SUM(ISERROR(Table1)*1,MIN(SUM(1-ISNUMBER(Table1[Two])),SUM(1-ISTEXT(Table1[Two]))))>0,NA(),SORT(UNIQUE(FILTER(Table1[Two],Table1[One]="a1"))))

Bit overcomplicated, but that's robot. We may skip error handling and take only FILTER part.

@ericGuyaderBerger 

Excel doesn't allow to use array formulas for data validation. You may generate spill by FILTER, even in another sheet (and hide it if necessary), use reference on this spill in data validation

image.png

@Sergei Baklan 

 

Thanks again for this new reply.

 

Spill reference works like a charm!  

@ericGuyaderBerger , you are welcome, glad it helped