• 540K Members
• 3,027 Online
• 644K Conversations

## Excel Help

Highlighted
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
Highlighted

# 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

Highlighted

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

Highlighted

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

Highlighted

# 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
IF statements and conditional formatting
clare1981 in Excel on
1 Replies
Reverse numbers and characters in worksheet
David_Gerrior in Excel on
1 Replies
Sumifs
Jsbluemoon82 in Excel on
3 Replies
Excel formula similiar to texjoin
Carlo74 in Excel on
0 Replies
everything is black and white in excel
Gold4trees in Excel on
1 Replies
Deleting unwanted rows and columns
chipg900 in Excel on
4 Replies