SOLVED
Home

Help reqd. in Index array function. Blank cells to be shown as "".

%3CLINGO-SUB%20id%3D%22lingo-sub-819353%22%20slang%3D%22en-US%22%3EHelp%20reqd.%20in%20Index%20array%20function.%20Blank%20cells%20to%20be%20shown%20as%20%22%22.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-819353%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22%22%3EThere%20are%202%20screen%20shots.%3CSTRONG%3EIn%20pic%201%20(Sheet%20name%20-%20%22Emp%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EReferring%20%24D%2412%20(Emp.%20code)%2C%20I%20have%20displayed%20Dealer%20codes%20from%20H23%20to%20H32%20(as%20shown%20in%20pic)%20using%20below%20array%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFERROR(INDEX(Main!%24H%246%3A%24H%242000%2CSMALL(IF(%24D%2412%3DMain!%24A%246%3A%24A%242000%2CROW(Main!%24A%246%3A%24A%242000)-MIN(ROW(Main!%24A%246%3A%24A%242000))%2B1%2C%22%22)%2CROW(A1)))%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPurpose%20of%20using%20this%20array%20formula%20is%20to%20show%20max%2010%20dealer%20codes%20at%20a%20time.%20If%20there%20are%20less%20dealers%2C%20say%204%20dealers%2C%20then%204%20codes%20will%20be%20displayed.%3C%2FP%3E%3CP%3E%3CSTRONG%3EReferring%3C%2FSTRONG%3E%3CSTRONG%3E%26nbsp%3Bpic%202%20(Sheet%20name%20-%20%22Main%22%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSTRONG%3Ein%20the%20same%20workbook%3CSTRONG%3E)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EEmp.%20code%20is%20also%20driven%20by%20below%20formula%20from%20%22Main%22%20sheet%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFERROR(VLOOKUP(%24D5%2CCHOOSE(%7B1%2C2%7D%2CMain!%24B%3A%24B%2CMain!%24A%3A%24A)%2C2%2C0)%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSTRONG%3EMy%20problem%3C%2FSTRONG%3E%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eis%20if%20an%20Emp.%20code%20is%20blank%20in%20%22Main%22sheet%20then%20%24D%2412%20shows%200%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3E(pic%203%20%26amp%3B%20pic%204)%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3EAnd%20cell%20H23%20to%20H32%20still%20shows%20dealer%20codes%20calculating%20from%20H6%20(from%20%22Main%22%20sheet)%20and%20so%20on..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20if%20%24D%2412%20is%200%20or%20%22-%22%20or%20blank%2C%20H23%20to%20H32%20should%20be%20blank.%26nbsp%3B%20If%20anybody%20can%20correct%20or%20simplify%20my%20formula%2C%20please%20help.%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F128141iB283A99D6D31C811%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222.jpg%22%20title%3D%222.jpg%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F128140i3E8120BC851CEB5D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%221.jpg%22%20title%3D%221.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-819353%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EINDEX%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-819410%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20reqd.%20in%20Index%20array%20function.%20Blank%20cells%20to%20be%20shown%20as%20%22%22.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-819410%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F380971%22%20target%3D%22_blank%22%3E%40Sarav45%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(OR(%24D%2412%3D%7B0%2C%22%22%7D)%2C%22%22%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EIFERROR(INDEX(Main!%24H%246%3A%24H%242000%2CAGGREGATE(15%2C6%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E1%2F(Main!%24A%246%3A%24A%242000%3D%24D%2412)*(ROW(Main!%24A%246%3A%24A%242000)-5)%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EROWS(%241%3A1)))%2C%22%22))%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-819506%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20reqd.%20in%20Index%20array%20function.%20Blank%20cells%20to%20be%20shown%20as%20%22%22.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-819506%22%20slang%3D%22en-US%22%3Ehi..Great%20thanks%20for%20reply..%20this%20formula%20is%20giving%20incorrect%20results.%3CBR%20%2F%3EAbove%20formula%20is%20working%20good%20but%20with%20a%20slight%20range%20change.%20I%20have%20to%20take%20whole%20column%20range%20to%20get%20correct%20results.%20If%20taking%20range%20H6%3AH2000%2C%20it%20is%20calculating%201st%20result%20(Nth%20row)%20from%20H6.%20So%20I%20selected%20whole%20column%20to%20solve%20this.%3CBR%20%2F%3EHere%20it%20is%20%3A%3CBR%20%2F%3E%3DIF(OR(%24D%2412%3D0%2C%22%22%7D)%2C%22%22%2CIFERROR(INDEX(Main!%24H%3A%24H%2CAGGREGATE(15%2C6%2C1%2F(Main!%24A%3A%24A%3D%24D%2412)*ROW(Main!%24A%3A%24A)%2CROWS(%241%3A1)))%2C%22%22))%3CBR%20%2F%3E%3CBR%20%2F%3EHow%20can%20I%20take%20specified%20range%20say%20H6%3AH2000%20only%2C%20just%20in%20case%20to%20avoid%20extra%20calculations%20in%20excel%20file.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-819520%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20reqd.%20in%20Index%20array%20function.%20Blank%20cells%20to%20be%20shown%20as%20%22%22.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-819520%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F380971%22%20target%3D%22_blank%22%3E%40Sarav45%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20define%20dynamic%20ranges%2C%20like%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DMain!%24H%246%3AINDEX(Main!%24H6%3A%24H1048576%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3ECOUNTA(Main!%24H%246%3A%24H%241048576))%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-819669%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20reqd.%20in%20Index%20array%20function.%20Blank%20cells%20to%20be%20shown%20as%20%22%22.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-819669%22%20slang%3D%22en-US%22%3Ethanks%20a%20ton..You%20are%20a%20gem..!!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-821066%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20reqd.%20in%20Index%20array%20function.%20Blank%20cells%20to%20be%20shown%20as%20%22%22.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-821066%22%20slang%3D%22en-US%22%3EYou%E2%80%99re%20very%20much%20welcome!%3C%2FLINGO-BODY%3E
Sarav45
Occasional Contributor

There are 2 screen shots.In pic 1 (Sheet name - "Emp")

Referring $D$12 (Emp. code), I have displayed Dealer codes from H23 to H32 (as shown in pic) using below array formula.

 

=IFERROR(INDEX(Main!$H$6:$H$2000,SMALL(IF($D$12=Main!$A$6:$A$2000,ROW(Main!$A$6:$A$2000)-MIN(ROW(Main!$A$6:$A$2000))+1,""),ROW(A1))),"")

 

Purpose of using this array formula is to show max 10 dealer codes at a time. If there are less dealers, say 4 dealers, then 4 codes will be displayed.

Referring pic 2 (Sheet name - "Main" in the same workbook)

Emp. code is also driven by below formula from "Main" sheet

 

=IFERROR(VLOOKUP($D5,CHOOSE({1,2},Main!$B:$B,Main!$A:$A),2,0),"")

 

My problem is if an Emp. code is blank in "Main"sheet then $D$12 shows 0 (pic 3 & pic 4).

And cell H23 to H32 still shows dealer codes calculating from H6 (from "Main" sheet) and so on..

 

I need if $D$12 is 0 or "-" or blank, H23 to H32 should be blank.  If anybody can correct or simplify my formula, please help.2.jpg1.jpg

5 Replies
Highlighted
Solution

@Sarav45 

Try this: 

=IF(OR($D$12={0,""}),"",

IFERROR(INDEX(Main!$H$6:$H$2000,AGGREGATE(15,6,

1/(Main!$A$6:$A$2000=$D$12)*(ROW(Main!$A$6:$A$2000)-5),

ROWS($1:1))),""))

hi..Great thanks for reply.. this formula is giving incorrect results.
Above formula is working good but with a slight range change. I have to take whole column range to get correct results. If taking range H6:H2000, it is calculating 1st result (Nth row) from H6. So I selected whole column to solve this.
Here it is :
=IF(OR($D$12=0,""}),"",IFERROR(INDEX(Main!$H:$H,AGGREGATE(15,6,1/(Main!$A:$A=$D$12)*ROW(Main!$A:$A),ROWS($1:1))),""))

How can I take specified range say H6:H2000 only, just in case to avoid extra calculations in excel file.

@Sarav45 

You may define dynamic ranges, like this: 

=Main!$H$6:INDEX(Main!$H6:$H1048576,

COUNTA(Main!$H$6:$H$1048576))

thanks a ton..You are a gem..!!
You’re very much welcome!
Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 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
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies