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