Forum Discussion
Excel Help
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.
4 Replies
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),"")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.
Hi,
You need to use the Advanced Filter to do so.
Please see this https://www.youtube.com/watch?v=yAbRJHkK4Mg&list=PLA6E69131CB6CD9C1 to learn more about it.
I've done that by Advanced Filter in the solution file.
Regards
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.
