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

 

[GazNicki]

Hi

What is in Column AA?

 

[AlanY]

try this array formula, confirm with shift-control-return together

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

 

[jcooooper]

Hmm I'm having the same problem... it works for the first entry then when I drag the formula down it blanks out.

I should specify, the formula is entered into cell AA4.... So it references the range above it as you drag it down

AA4 will reference $AA$2:AA3

AA23 will reference $AA$2:AA22

 

[jcooooper]

This is the unique list that the formula creates....it references the cells above it and grows dynamically as the forumla is dragged down...

AA4 will reference $AA$2:AA3

AA23 will reference $AA$2:AA22

etc etc

 

[AlanY]

try this, again an array formula

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

Cell Formulas
Range	Formula
Y4	{=IFERROR(LOOKUP(2,1/(COUNTIF($Y$2:Y3,IF($I$3:$I$34<>0,$H$3:$H$34))=0),IF($I$3:$I$34<>0,$H$3:$H$34)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jcooooper]

try this, again an array formula

	H	I	Y
1
2
3	3	1
4	4	1	FALSE
5	5	1	23
6	6	1	22
7	7		21
8	8		20
9	9		19
10	10		18
11	11		17
12	12	1	14
13	13	1	13
14	14	1	12
15	15		6
16	16		5
17	17	1	4
18	18	1	3
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​

Array Formulas Cell Formula Y4 {=IFERROR(LOOKUP(2,1/(COUNTIF($Y$2:Y3,IF($I$3:$I$34<>0,$H$3:$H$34))=0),IF($I$3:$I$34<>0,$H$3:$H$34)),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Ok this pretty much works..thanks! Only thing though is it is returning a false for those that have a 0..... Is there anyway to avoid the FALSE (can't have a blank either)

 

[AlanY]

i thought you might ask that.

the easiest way is to format the cell font colour as the fill colour, like this

Book1
	H	I	Y
1
2
3	3	1
4	4	1	FALSE
5	5	1	23
6	6	1	22
7	7		21
8	8		20
9	9		19
10	10		11
11	11		17
12	11		6
13	11	1	5
14	11	1	4
15	15		3
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]

Yeah the issue is I am creating another table from this table, and it is dependant on a unique list with no blanks/falses/columns with 0 value as mentioned above....tricky one...I've tried a couple of people at work who are also stumped

 

[AlanY]

so, what will work in Cell Y4 above for the unique list?