How to return all corresponding values that meet 2 criteria in excel

Like this?

Try this array formula**.

Let's assume you enter the formula in cell L2.

=IFERROR(INDEX(A:A,SMALL(IF(C$2:C$100=G$4,IF(E$2:E$100=H$4,ROW(E$2:E$100))),ROWS(L$2:L2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

Originally Posted by sgw_73

Tony - Your formula works, but only returns the first entry that matches the search criteria, which is the same problem I have with my formula above.

I have attached the data I'm using.

In your file you say you want the data from column C that meets the conditions but in your first post you say you want the data from column A.

The formula I suggested does what you want. You just have to put the range references in the right places. Also, make sure you array enter** the formula.

Here's the formula changed to return the data from column C as you note in the file. In this example I enter the array formula** in cell G1 then copy down:

=IFERROR(INDEX(C:C,SMALL(IF(A$2:A$2055=E$1,IF(B$2:B$2055=F$1,ROW(B$2:B$2055))),ROWS(G$1:G1))),"")

** Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you must use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you must use the key combo to produce them. Also, anytime you edit an array formula it *must* be re-entered as an array using the key combo.

There are only 2 instances that meet the conditions:

Account Manager
Territory Manager

You can test this by using this formula to count the instances:

=COUNTIFS(A:A,E1,B:B,F1)

Result = 2

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.