• 414K Members
• 7,167 Online
• 476K Conversations
SOLVED

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

Occasional Contributor

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

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.

5 Replies
Solution

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

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))),""))

Highlighted

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

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.

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

You may define dynamic ranges, like this:

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

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

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

thanks a ton..You are a gem..!!

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

You’re very much welcome!
Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 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
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies