List return

[vgresia]

Hi there - I am looking to create a dynamic list of unique ID numbers for items matching "Horse" in column J, "Ball" in column I, and that do not contain the word dog in column A. In the example below, this would return the ID numbers 48 and 56. However, the formula would be entered into Sheet 1, while the data is housed on Sheet 2.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]C[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]Pet[/TD]
[TD]Id Number[/TD]
[TD]Toy[/TD]
[TD]Animal[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]12[/TD]
[TD]Ball[/TD]
[TD]Horse[/TD]
[/TR]
[TR]
[TD]Doggy[/TD]
[TD]56[/TD]
[TD]Chew[/TD]
[TD]Cow[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]89[/TD]
[TD]Ball[/TD]
[TD]Pig[/TD]
[/TR]
[TR]
[TD]Bird[/TD]
[TD]48[/TD]
[TD]Ball[/TD]
[TD]Horse[/TD]
[/TR]
[TR]
[TD]Doggy[/TD]
[TD]95[/TD]
[TD]Ball[/TD]
[TD]Cow[/TD]
[/TR]
[TR]
[TD]Doggy[/TD]
[TD]89[/TD]
[TD]Chew[/TD]
[TD]Horse[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]56[/TD]
[TD]Ball[/TD]
[TD]Horse[/TD]
[/TR]
[TR]
[TD]Bird[/TD]
[TD]56[/TD]
[TD]Ball[/TD]
[TD]Horse[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hi,

For which reason is ... 12 ... excluded ...?

 

[vgresia]

Because it contains "dog" in column A

 

[Eric W]

Perhaps:

	A	B	C	D
A Exclude value	dog	ID numbers
I Include	Ball
J include	horse

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]48[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]56[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet2!$C$2:$C$20,SMALL(IF(ISERROR(SEARCH($B$1,Sheet2!$A$2:$A$20))*ISNUMBER(SEARCH($B$2,Sheet2!$I$2:$I$20))*ISNUMBER(SEARCH($B$3,Sheet2!$J$2:$J$20))*(COUNTIF($C$1:$C1,Sheet2!$C$2:$C$20)=0),ROW(Sheet2!$A$2:$A$20)-ROW(Sheet2!$A$2)+1),ROWS($C$2:$C2))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



I suspect this can be improved, I'll look a bit more at it, and maybe someone else can jump in.

 

[vgresia]

Brilliant, worked like a charm!

 

[Eric W]

Glad it works for you!

 

[vgresia]

Ah I spoke a little too soon! It is working for listing the first value, when I drag the formula down though it is not recognizing the second ID number. Any thoughts?

 

[Eric W]

Are you aware that this is an array formula? To use an array formula, enter it in the formula bar, then hold down the Control and Shift keys, then press enter. If you've already entered it into the cell, select the cell, press F2, then hold down Control and Shift and press enter. Now you can drag it down.

If it still doesn't work, let me know and I'll see what else it could be.

 

[vgresia]

Yes, I am entering it as an array and using ctrl+shift+enter for the cell and then pulling it down. It is working for the first value but the other values are coming up blank. In your formula should the cell in the second row be (just $C1 changes to $C2 and $C2 changes to $C3):
{=IFERROR(INDEX(Sheet2!$C$2:$C$20,SMALL(IF(ISERROR(SEARCH($B$1,Sheet2!$A$2:$A$20))*ISNUMBER(SEARCH($B$2,Sheet2!$I$2:$I$20))*ISNUMBER(SEARCH($B$3,Sheet2!$J$2:$J$20))*(COUNTIF($C$1:$C2,Sheet2!$C$2:$C$20)=0),ROW(Sheet2!$A$2:$A$20)-ROW(Sheet2!$A$2)+1),ROWS($C$2:$C3))),"")}

 

[Eric W]

Nuts!!

I wrote a whole long explanation of how the formula works, only to realize that I had a bug in it!

Here's what the C2 formula should be (with CSE):

=IFERROR(INDEX(Sheet2!$C$2:$C$20,SMALL(IF(ISERROR(SEARCH($B$1,Sheet2!$A$2:$A$20))*ISNUMBER(SEARCH($B$2,Sheet2!$I$2:$I$20))*ISNUMBER(SEARCH($B$3,Sheet2!$J$2:$J$20))*(COUNTIF($C$1:$C1,Sheet2!$C$2:$C$20)=0),ROW(Sheet2!$A$2:$A$20)-ROW(Sheet2!$A$2)+1),1)),"")

The $C$1:$C1 range in the COUNTIF is correct, make sure it refers to the cell above the cell where you put the formula. Let me know if this works any better.