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
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies