Help Needed

[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 

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

See attached file.

[#3]

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

[#4]

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.

[#5]

@ian_122282 

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.