• 462K Members
• 6,733 Online
• 559K 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
Highlighted
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))),""))

# 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
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
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies