Home

Help Needed

%3CLINGO-SUB%20id%3D%22lingo-sub-894698%22%20slang%3D%22en-US%22%3EHelp%20Needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-894698%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Excel%20Community%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20improve%20my%20excel%20skill%20but%20I%20hit%20a%20roadblock%20here.%20I%20am%20trying%20to%20do%20research%20but%20I%20can't%20find%20a%20clear%20answer%2C%20here%20is%20my%20scenario%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20I%20want%20to%20filter%20the%20fruits%20in%20Columns%20A-B%20with%20sales%20of%20%241500%20-%20%242000.%20The%20results%20should%20be%20put%20in%20columns%20G-H.%20What%20is%20the%20formula%20to%20do%20this%3F%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E2.%20Using%20the%20same%20data%2C%20filter%20the%20fruits%20that%20have%20sales%20of%20%242000%20and%20up.%20What%20is%20the%20formula%20to%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20the%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-894698%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-894786%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20Needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-894786%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420500%22%20target%3D%22_blank%22%3E%40ian_122282%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20need%20a%20helper%20column%20and%20a%20reference%20table%20to%20categorize%20your%20data.%3C%2FP%3E%3CP%3ESee%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-895007%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20Needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-895007%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B-%20thanks%20for%20your%20assistance%20on%20this.%3C%2FP%3E%3CP%3EI%20will%20review%20this%20now%20and%20study%20your%20approach.%3C%2FP%3E%3CP%3EI%20will%20get%20back%20to%20you%20just%20in%20case%20I%20have%20a%20question.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-895022%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20Needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-895022%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420500%22%20target%3D%22_blank%22%3E%40ian_122282%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CEM%3ERefer%20to%20the%20attached%20sample%20file%3C%2FEM%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3ELet%20me%20offer%20an%20alternate%20approach%3A%3C%2FP%3E%3CP%3EIn%20cell%20F5%2C%20the%20formula%3A%3C%2FP%3E%3CP%3E%3DIF(VLOOKUP(%24E5%2C%24A%243%3A%24B%2422%2C2%2CFALSE)%26lt%3B1500%2CVLOOKUP(%24E5%2C%24A%243%3A%24B%2422%2C2%2CFALSE)%2C%22%22)%3C%2FP%3E%3CP%3ESearches%20for%20%22Pears%22%20in%20the%20range%20A3%3AB22%20and%20returns%20a%20value%20if%20Pear%20sales%20%26lt%3B%241500%2C%20else%20the%20cell%20will%20return%20%22%22%20or%20blank.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20cell%20G3%2C%20the%20formula%3A%3C%2FP%3E%3CP%3E%3DIF(AND(VLOOKUP(%24E3%2C%24A%243%3A%24B%2422%2C2%2CFALSE)%26gt%3B%3D1500%2CVLOOKUP(%24E3%2C%24A%243%3A%24B%2422%2C2%2CFALSE)%26lt%3B2000)%2CVLOOKUP(%24E3%2C%24A%243%3A%24B%2422%2C2%2CFALSE)%2C%22%22)%3C%2FP%3E%3CP%3ESearches%20for%20%22Apples%22%20in%20the%20range%20A3%3AB22%20and%20returns%20a%20value%20if%20Apple%20sales%20%26gt%3B%3D%241500%20AND%20%26lt%3B%242000%2C%20else%20the%20cell%20will%20return%20%22%22%20or%20blank.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20cell%20H7%2C%20the%20formula%3A%3C%2FP%3E%3CP%3E%3DIF(VLOOKUP(%24E7%2C%24A%243%3A%24B%2422%2C2%2CFALSE)%26gt%3B%3D2000%2CVLOOKUP(%24E7%2C%24A%243%3A%24B%2422%2C2%2CFALSE)%2C%22%22)%3C%2FP%3E%3CP%3ESearches%20for%20%22Lemons%22%20in%20the%20range%20A3%3AB22%20and%20returns%20a%20value%20if%20Lemon%20sales%20%26gt%3B%3D%242000%2C%20else%20the%20cell%20will%20return%20%22%22%20or%20blank.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-895242%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20Needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-895242%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420500%22%20target%3D%22_blank%22%3E%40ian_122282%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EJust%20for%20the%20collection%20here%20is%20Power%20Query%20solution.%20D1%3AI1%20is%20a%20named%20range%20and%20below%20actually%203%20separate%20tables%20(one%20per%20range)%20returned%20by%20queries.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20571px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F135551i1658BB1D49972E94%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20is%20practical%20-%20it's%20a%20bad%20idea%20to%20merge%20cells%2C%20better%20to%20avoid%20the%20merging.%20Instead%2C%20select%20cells%2C%20Ctrl%2B1%20and%20center%20the%20content%20across%20selection.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20305px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F135552i603FAA94B9319B7A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
ian_122282
Occasional Contributor

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

@ian_122282 

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.

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

image.png

 

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.

image.png

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies