Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Old Index/Match not doing as designed

0

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

Answer
Discuss

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: 4889) 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
Add to Discussion

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.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login