0

I would like to be able to match multiple criteria and return multiple rows and columns without using an array in Excel 2010. I am currently using the below for a single criteria and am able to return multiple rows and columns without using an array:

=INDEX($A$7:$AO$4500, SMALL(INDEX(($AN$1=$B$7:$B$4500)*(MATCH(ROW($B$7:$B$4500), ROW($B$7:$B$4500)))+($AN$1<>$B$7:$B$4500)*1048577, 0, 0),ROW(A1)),COLUMN(A1))

Table is in A7:AO4500

AN1 = Today()
Row B = Due Date
Row AZ = Ship Date (looking for "")
Improve this question
3
  • I think the common response to Excel questions is to ask to post an example of the data you're working with.
    – Darren
    Commented Dec 7, 2017 at 14:33
  • That comment is just a mess. Please edit your question and add the information to it. Please use the correct formatting tags.
    – Darren
    Commented Dec 7, 2017 at 14:49
  • For better understanding provide the LINK of the Sheet or upload the Sample Data along with the Criteria list.
    – Rajesh Sinha
    Commented Dec 9, 2017 at 10:24

1 Answer 1

Reset to default
0

Since, no sample Data has been provide therefore I've decide to use my Sample Data to extract more than one records on multiple criteria lookup.

NB: Only an Array Formula can do this.

Check the Screen Shot.

enter image description here

The Formula I've used is,

=INDEX($A$36:$C$45,SMALL(IF(COUNTIF($E$34,$B$36:$B$45)*COUNTIF($F$34, $C$36:$C$45),ROW($A$36:$C$45)-MIN(ROW($A$36:$C$45))+1),ROW(A1)), COLUMN(A1))

The Formula is using Criteria in Cells E34 & F34 are 100 & 50.

Note , Multiplication sign used to connect both COUNTIF is working as AND.

Below the Database, you can find two filtered Rows matching the Criteria.

I do believe this example helps you and you can easily extend the scope of the exercise.

NB: If you are fixed with Non-Array Formula solution, I can suggest you Tricks with Advance Filter.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .