old index match formula not doing what's needed

KDS14589

Board Regular
Joined
Jan 10, 2019
Messages
54
Office Version
  1. 2016
Platform
  1. Windows
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!Z13: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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

KDS14589

Board Regular
Joined
Jan 10, 2019
Messages
54
Office Version
  1. 2016
Platform
  1. Windows
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 can included 2 screen shots and a file.
I have a sanitized file of this program but I can't figure out how to send them to you. HELP
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,284
Office Version
  1. 365
Platform
  1. Windows
You can upload the file to a share site such as OneDrive, DropBox, mark for sharing & post the link to the thread.
Another option is you can use one of the add-ins available here, that enable you to include a small sample of your data in the post. Add-ins
 

Watch MrExcel Video

Forum statistics

Threads
1,108,950
Messages
5,525,822
Members
409,665
Latest member
Jessyi

This Week's Hot Topics

Top