Aug 22 2019 10:37 PM
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.
Aug 22 2019 11:27 PM - edited Aug 23 2019 12:42 AM
SolutionTry 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))),""))
Aug 23 2019 01:07 AM
Aug 23 2019 01:18 AM
You may define dynamic ranges, like this:
=Main!$H$6:INDEX(Main!$H6:$H1048576,
COUNTA(Main!$H$6:$H$1048576))
Aug 23 2019 02:48 AM
Aug 23 2019 05:33 PM
Aug 22 2019 11:27 PM - edited Aug 23 2019 12:42 AM
SolutionTry 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))),""))