Forum Discussion
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
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.
You need a helper column and a reference table to categorize your data.
See attached file.
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.
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.
