 • 394K Members
• 8,065 Online
• 426K Conversations
SOLVED

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

# 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