Unique list using Lookup, Countif, need one additional criteria

[jcooooper]

Hello all,

I stumbled across a wonderful formula which works perfect, but the logic is beyond my comprehension, so I'm having difficulty adding one bit of additional criteria.

In column H, I have a list of text strings, some of which are repeated

=IFERROR(LOOKUP(2,1/(COUNTIF($AA$2:AA3,$H$3:$H$34)=0),$H$3:$H$34),"")

The above creates a unique list from the list in column H. I drag this formula down and works fine...

What I need is it to exclude this name from the list if the corresponding value in column I is 0.

I tried this:

=IFERROR(LOOKUP(2,1/(COUNTIFS($AA$2:AA2,$H$3:$H$34,I4,"<>0")=0),$H$3:$H$34),"")

To no avail. The first name in the list pops up but then the rest start returning blanks.

Any help would be greatly appreciated

Thanks,
JC

 

[AlanY]

will put a dummy False above works for you?

Book1
	H	I	Y
1
2			FALSE
3	3	1
4	4	1	23
5	5	1	22
6	6	1	21
7	7		20
8	8		19
9	9		11
10	10		17
11	11		6
12	11		5
13	11	1	4
14	11	1	3
15	15
16	16
17	17	1
18	11	1
19	19	1
20	20	1
21	21	1
22	22	1
23	23	1
Sheet6

 

[jcooooper]

will put a dummy False above works for you?

	H	I	Y
1
2			FALSE
3	3	1
4	4	1	23
5	5	1	22
6	6	1	21
7	7		20
8	8		19
9	9		11
10	10		17
11	11		6
12	11		5
13	11	1	4
14	11	1	3
15	15
16	16
17	17	1
18	11	1
19	19	1
20	20	1
21	21	1
22	22	1
23	23	1

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet6​

Ah yes this works! Jsut throwing a FALSE up top.

Thanks so much, this will save me a huge amount of time.... Really appreciate the help

 

[AlanY]

you're welcome