# Help Needed

Highlighted
Occasional Contributor

# Help Needed

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: Help Needed

You need a helper column and a reference table to categorize your data.

See attached file.

Highlighted

# Re: Help Needed

@Detlef Lewin - 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: Help Needed

Hello @ian_122282,

Refer to the attached sample file

Let me offer an alternate approach:

In cell F5, the formula:

=IF(VLOOKUP(\$E5,\$A\$3:\$B\$22,2,FALSE)<1500,VLOOKUP(\$E5,\$A\$3:\$B\$22,2,FALSE),"")

Searches for "Pears" in the range A3:B22 and returns a value if Pear sales <\$1500, else the cell will return "" or blank.

In cell G3, the formula:

=IF(AND(VLOOKUP(\$E3,\$A\$3:\$B\$22,2,FALSE)>=1500,VLOOKUP(\$E3,\$A\$3:\$B\$22,2,FALSE)<2000),VLOOKUP(\$E3,\$A\$3:\$B\$22,2,FALSE),"")

Searches for "Apples" in the range A3:B22 and returns a value if Apple sales >=\$1500 AND <\$2000, else the cell will return "" or blank.

In cell H7, the formula:

=IF(VLOOKUP(\$E7,\$A\$3:\$B\$22,2,FALSE)>=2000,VLOOKUP(\$E7,\$A\$3:\$B\$22,2,FALSE),"")

Searches for "Lemons" in the range A3:B22 and returns a value if Lemon sales >=\$2000, else the cell will return "" or blank.

Highlighted

# Re: Help Needed

Just for the collection here is Power Query solution. D1:I1 is a named range and below actually 3 separate tables (one per range) returned by queries.

What is practical - it's a bad idea to merge cells, better to avoid the merging. Instead, select cells, Ctrl+1 and center the content across selection.