Forum Discussion
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...
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.
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.