Ttdfjt

I am trying to setup a formula that returns values that satisfy two criteria.
E.g.

 A B C
1 Bob Banana 10 
2 John Banana 10
3 Steven Apple 5
4 Elliott Apple 2
5 Andrew Apple 5

I want to return a list of names with the values in column B and C that equal Apple and 5, respectively.

I have used a variant of the formula below to successfully return one value but am having trouble getting the second value into this formula

=IFERROR(INDEX($A$1:$A$4,SMALL(IF($B$1:$B$4=$D$1,ROW($B$1:$B$4)-ROW($B$1)+1),ROWS($B$1:$B1))),"")

if someone could help me work that is out that would be awesome. Thanks.

This Array formula can solve the issue:

=IFERROR(INDEX($J$38:$L$42, SMALL(IF(COUNTIFS($J$44, $K$38:$K$42, $K$44, $L$38:$L$42), ROW($J$38:$L$42)-MIN(ROW($J$38:$L$42))+1), ROW(A1)), COLUMN(A1)),"")

N.B.

• Finish formula in Cell J46, with Ctrl+Shift+Enter, fill Right then Down.


• Cell J44 & K44 comprises criteria

• Your Formula search values only in one Column,,
  =IFERROR(INDEX($A$1:$A$4
  


• Also it matches only one criteria instead of two,

IF($B$1:$B$4=$D$1

Now, let me describe the mechanism behind the Array formula:

 =INDEX($J$38:$L$42, SMALL(False, False, 3, False, 5, ROW(A1)), COLUMN(A1)) 

becomes,

=INDEX($J$38:$L$42, 3, 5)

Returns Names then by dragging Right & Down the others values.

(IF(COUNTIFS($J$44, $K$38:$K$42, $K$44, $L$38:$L$42), ROW($J$38:$L$42)-MIN(ROW($J$38:$L$42))+1)

Returns,

IF(0, 0, 1, 0, 1,, 1, 2, 3, 4, 5)

And finally the combination of TRUE/FALSE & row number 3 & 5 like this,

False,False, 3, False, 5 get combined with INDEX & SMALL, shown above also.

• Formula is wrapped with IFERROR to replace error with blanks, if formula doesn't finds the value.

Adjust cell references in Formula as needed.

Rajesh S ran with the approach you attempted and demonstrated how to make that work. If the data is dynamic, that has you covered. But if you only need to periodically extract a results table, I'll toss in a couple of really simple alternatives. These can be done in minutes, even if you're out of coffee for your brain.

Filtering

In the image below, I deselected the filters after copying the results so you could see the source data.

• Highlight the data you want to use for criteria.


• Turn on filtering.


• Select the values you want in the filters. This will hide everything you don't want.


• Select what's displayed, copy it, paste it to your results area.

Need different criteria? Select the new criteria. Copy and paste.

Pivot Table

For a given set of criteria, every qualifying record will have a name and then repeat the criteria on each record. If you are generating reports for different criteria, you will want to keep the criteria with the result. But if you don't need it on every record, a pivot table offers a fast way to do it.

• Highlight the data.


• From the toolbar or menu, Insert Pivot Table.


• Drag the Name field to the Rows window. Drag the Fruit and Number fields to the filters.


• Select your criteria in the filters.

The result is the list of qualifying names, with the criteria summarized in the heading.

You can copy the pivot table and have two pasting choices. A normal paste will give you another pivot table. You can change the filter criteria in it for another case. Paste Special, Values will paste a copy of the results.