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

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

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

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

[TD="align: center"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]23[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]22[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]21[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]20[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]19[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]11[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]17[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]6[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]5[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]4[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]3[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]

</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