Forum Discussion

ian_122282's avatar
ian_122282
Copper Contributor
Oct 05, 2019

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 t...
excel
Formulas and Functions
ian_122282's avatar
ian_122282
Copper Contributor
Oct 05, 2019

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.

PReagan's avatar
PReagan
Bronze Contributor
Oct 05, 2019

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.

Resources