I just found out an array index/match I wrote awhile back isn't doing what I needed. the formula is
=IFERROR(INDEX(General.Lists!AV13:AV338, SMALL(IF(COUNTIF($CC$14,General.Lists!AN13:AN338)*COUNTIF($CC$15,General.Lists!S13:S338)*COUNTIF($CC$16,General.Lists!Z1 3:Z338), ROW(General.Lists!AV13:AV338)-MIN(ROW(General.Lists!A13:A338))+1), ROW(A1)), COLUMN(A1)),"")
it's skipping some match's, why I'm not sure.
what I want is a match of CC14 to the info. in General.Lists!an13:an338 also
what I want is a match of CC15 to the info. in General.Lists!s13:s338 also
what I want is a match of CC16 to the info. in General.Lists!z13:z338
if all the criteria match I want ALL the matches to give me the result from General.Lists!AV13:AV338
the formula then is CSE, placed into cell BS14 and dragged down to BS352
The old formula skipped matches, sorry its be awhile and I can't remember where I got it.
PS. one reason I'm reworking this is I also want the resulting matches from General.Lists!AV13:AV338 to become alphabetized. I'm willing to insert/hide helper columns if needed
Old Index/Match not doing as designed
0
Discussion
This needs real data for testing. Please post your workbook with plenty of actual data. Be sure not to change their type if they need sanitising before publication here.
Variatus (rep: 3533) Oct 31, '19 at 7:38 pm
i can but maybe tommorrow
KDS1489 (rep: 6) Oct 31, '19 at 9:44 pm
Can u send file so that we can try
beepetark Nov 1, '19 at 3:15 am
Success on the finial
Thanks to everyone for their help in solving the difficult parts
KDS1489 (rep: 6) Nov 12, '19 at 11:20 am
Answers
0
Selected Answer
I sanitized this program.
I added some ranges to file so the formula in question is easier to understand but the final formula is the same. Just now it returns just one result.
My ultimate quest is to return ALL the matches on worksheet LISTS that match all 3 criteria on the Restrictions worksheet.
I'm using 2016 Microsoft Office the Excel program
I've also included 2 screen shots if you can't open the file.