• 410K Members
• 10.4K Online
• 466K Conversations

## Excel Help

Occasional Contributor

# 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

# Re: Excel Help

@ian_122282

Hi,

You need to use the Advanced Filter to do so.

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

Regards

# Re: Excel Help

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),"")

# Re: Excel Help

@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.

# Re: Excel Help

@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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies