Listing All Matches Based On Criteria

[Lewis825]

Hello,

I have survived this far on trolling through forums to get the answers I need, but the time has come to ask the questions myself!

I have a formula which lists all matches based on 3 criteria.

{=IFERROR(INDEX('Drop Downs'!$U$2:$U$30790,(SMALL(IF(('Drop Downs'!$W$2:$W$30790=$E$1)*('Drop Downs'!$X$2:$X$30790=$E$2)*('Drop Downs'!$Y$2:$Y$30790=$E$3),ROW('Drop Downs'!$U$2:$U$30790)),ROWS('Drop Downs'!$U$2:$U2)))),"")}

This works fine and isn't the issue.

The values in E1, E2, & E3 are output from a drop down. The user selects the criteria in the drop down and the values show accordingly.

However, one of the choices in the drop down is ALL. meaning they want to list matches against all the variables.

I know that normally i could us a "<>"&"" to match non blank cells. but this doesn't seem to work for the above formula.


So in short,

If: E1=ALL E2=Blue E3=Square

match all values in W2:W30790
Match Only Blue in X2:X30790
Match Only Square in Y2:Y30790

then, list all values in U2:U30790 that match the above criteria


Hope this is enough to go on

Cheers
Lewis

 

[crazydragon84]

"*" this is wildcard for anything. try that.

 

[markmzz]

Hi Lewis!

Try this small modification in your formula:

=IFERROR(INDEX('Drop Downs'!$U$2:$U$30790,(SMALL(IF(
('Drop Downs'!$W$2:$W$30790=IF($E$1="All";'Drop Downs'!$W$2:$W$30790;$E$1)*
('Drop Downs'!$X$2:$X$30790=IF($E$2="All";'Drop Downs'!$X$2:$X$30790;$E$2)*
('Drop Downs'!$Y$2:$Y$30790=IF($E$3="All";'Drop Downs'!$Y$2:$Y$30790;$E$3),
ROW('Drop Downs'!$U$2:$U$30790)),ROWS('Drop Downs'!$U$2:$U2)))),"")

Markmzz
​

 

[Lewis825]

Mark that's brill,

This worked for the issues i was having.

It worked so well i wanted to apply the same formula to some other areas as it's much simpler than the Looooong formulas i had written out myself.

However, It doesn't seem to work perfectly.

{=IFERROR(INDEX('Drop Downs'!$F$2:$F$1814,(SMALL(IF(
('Drop Downs'!$E$2:$E$1814=IF($E$1="All",'Drop Downs'!$E$2:$E$1814,$E$1))*
('Drop Downs'!$D$2:$D$1814=IF($E$2="All",'Drop Downs'!$D$2:$D$1814,$E$2)),
ROW('Drop Downs'!$F$2:$F$1814)),ROWS('Drop Downs'!$F$2:$F2)))),"")}

I basically want to show all the values in F2:F1814 where the the values in E & D2:D1814 match. Based on the values in E1 & E2

However, while it returns values, its returning values that don't match up, Where E & D don't match?

Am i missing something?

Regards
Lewis

 

[Lewis825]

Fear not,

Appears i was missing a '-1'

Solved

{=IFERROR(INDEX('Drop Downs'!$F$2:$F$1814,(SMALL(IF(
('Drop Downs'!$E$2:$E$1814=IF($E$1="All",'Drop Downs'!$E$2:$E$1814,$E$1))*
('Drop Downs'!$D$2:$D$1814=IF($E$2="All",'Drop Downs'!$D$2:$D$1814,$E$2)),
ROW('Drop Downs'!$F$2:$F$1814)-1),ROWS('Drop Downs'!$F$2:$F2)))),"")}

Thanks again for your help

 

[markmzz]

Mark that's brill,
This worked for the issues i was having.
It worked so well i wanted to apply the same formula to some other areas as it's much simpler than the Looooong formulas i had written out myself.
However, It doesn't seem to work perfectly.

{=IFERROR(INDEX('Drop Downs'!$F$2:$F$1814,(SMALL(IF(
('Drop Downs'!$E$2:$E$1814=IF($E$1="All",'Drop Downs'!$E$2:$E$1814,$E$1))*
('Drop Downs'!$D$2:$D$1814=IF($E$2="All",'Drop Downs'!$D$2:$D$1814,$E$2)),
ROW('Drop Downs'!$F$2:$F$1814)),ROWS('Drop Downs'!$F$2:$F2)))),"")}

I basically want to show all the values in F2:F1814 where the the values in E & D2:D1814 match. Based on the values in E1 & E2
However, while it returns values, its returning values that don't match up, Where E & D don't match?
Am i missing something?
Regards
Lewis

Fear not,
Appears i was missing a '-1'
Solved

{=IFERROR(INDEX('Drop Downs'!$F$2:$F$1814,(SMALL(IF(
('Drop Downs'!$E$2:$E$1814=IF($E$1="All",'Drop Downs'!$E$2:$E$1814,$E$1))*
('Drop Downs'!$D$2:$D$1814=IF($E$2="All",'Drop Downs'!$D$2:$D$1814,$E$2)),
ROW('Drop Downs'!$F$2:$F$1814)-1),ROWS('Drop Downs'!$F$2:$F2)))),"")}

Thanks again for your help

Hi Lewis,

You are welcome and thanks for the return.

By the way, try this small modification in your array formula too:

use Ctrl+Shift+Enter to enter the formula

=IFERROR(INDEX('Drop Downs'!$F$2:$F$1814,SMALL(
IF('Drop Downs'!$E$2:$E$1814=IF($E$1="All",'Drop Downs'!$E$2:$E$1814,$E$1),
IF('Drop Downs'!$D$2:$D$1814=IF($E$2="All",'Drop Downs'!$D$2:$D$1814,$E$2),
ROW('Drop Downs'!$F$2:$F$1814)-ROW('Drop Downs'!$F$2)+1)),ROWS($F$2:$F2))),"")

Markmzz

 

[Lewis825]

Hi Mark,

Thanks for the mod,

Are you able to breifly explain what this section of the code does?

Cheers
Lewis

 

[markmzz]

Hi Mark,

Thanks for the mod,

Are you able to breifly explain what this section of the code does?

Cheers
Lewis

Hi Lewis,

I think that the best way to explain is with a example.

Try this:

If you have the range below:

'Drop Downs'!$F$5:$F$1817

Try your formula and my suggestion

ROW('Drop Downs'!$F$5:$F$1817)-1

ROW('Drop Downs'!$F$5:$F$1817)-ROW('Drop Downs'!$F$5)+1

And tell me the results.

Or try only to move the range 'Drop Downs'!$F$2:$F$1814 to 'Drop Downs'!$F$5:$F$1817.

Markmzz