Excel Help

[ian_122282]

Hello Excel Community,

I am trying to improve my excel skill but I hit a roadblock here. I am trying to do research but I can't find a clear answer, here is my scenario;

1. I want to filter the fruits in Columns A-B with sales of $1500 - $2000. The results should be put in columns G-H. What is the formula to do this?

2. Using the same data, filter the fruits that have sales of $2000 and up. What is the formula to do this?

Thanks in advance for the help.

[#2]

@ian_122282

Hi,

You need to use the Advanced Filter to do so.

Please see this playlist to learn more about it.

I've done that by Advanced Filter in the solution file.

Regards

[#3]

@ian_122282 

In the attached file, the formula in E3 is: 

=IFNA(LOOKUP(2,1/(($B$2:$B$21=AGGREGATE(15,6,$B$2:$B$21/($B$2:$B$21<1500),ROW()-2))*
(COUNTIF(E$2:E2,$A$2:$A$21)=0)),
$A$2:$A$21),"")

In G3, the formula is modified like this: 

=IFNA(LOOKUP(2,1/(($B$2:$B$21=AGGREGATE(15,6,$B$2:$B$21/(($B$2:$B$21>=1500)*
($B$2:$B$21<2000)),ROW()-2))*(COUNTIF(G$2:G2,$A$2:$A$21)=0)),
$A$2:$A$21),"")

In I3, the formula is again modified like this: 

=IFNA(LOOKUP(2,1/(($B$2:$B$21=AGGREGATE(15,6,$B$2:$B$21/($B$2:$B$21>=2000),ROW()-2))*
(COUNTIF(I$2:I2,$A$2:$A$21)=0)),
$A$2:$A$21),"")

In F3, H3, and J3, this formula is copied therein: 

=IFNA(LOOKUP(2,
1/($A$2:$A$21=E3),
$B$2:$B$21),"")

[#4]

@Twifoo - thanks for your assistance on this.

I will review this now and study your approach.

I will get back to you just in case I have a question.

[#5]

@Haytham Amairah - thanks for your assistance on this.

I will review this now and study your approach.

I will get back to you just in case I have a question.