INDEX MATCH for multiple criteria

[chaps07187]

I am trying to pull from a database with multiple columns and match criteria to vertical rows. Here is my formula.

=IFERROR(INDEX(name,MATCH(1,($B$1=INDEX(Table1, ,$C$1))*($A$4=shift)*(B$5=unit)*($A$6=job),0)),)

My data is pulling correct information but only one value when multiple values exist. How could I add a SMALL function to this formula?

[FlameRetired]

Hello chaps07187. Welcome to the forum.

As you have discovered the MATCH/LOOKUP type functions return the first match they find. To return multiple values we typically assign index (row or column) numbers, then return those and pass them to INDEX.

Try this in first output cell and fill down until you get blanks.

Please Login or Register  to view this content.

[chaps07187]

Thank you this solved all issues

What does AGGREGATE(15,6, refer to?

[FlameRetired]

You are welcome. Thank you for the feedback.\

AGGREGATE is like the "Swiss Army Knife" of functions. It can perform 19 different functions each referenced by number. 15 is the Small function and does everything that the stand alone SMALL function does.

The 6 is one of several options to ignore errors. In this case it ignores an array dominated by #DIV/0! errors.

While this formula is an array formula AGGREGATE usually removes the requirement that it be committed with Ctrl + Shift + Enter and can be committed with just plain Enter.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Edit If you are interested in more you might like this link. http://www.exceluser.com/excel_help/...-aggregate.htm