Hey, Bandersnatch. Just wanted to drop a note offline about answer edits. Be careful with adding derogatory comments as part of an answer edit. If a mod sees it, you're likely to get a suspension for vandalizing the post. I understand the sentiment, motivation, and frustration, but it's really taboo.

At least if you confine it to comments, the repercussions might be just a "be nice" warning. Beyond comments and downvotes, it would probably be better to just refer the bad answer problem to the mods to deal with.

I'm not sure there's anything even they can do in this case because the bad answer poster seems incorrigible, but has managed to acquire substantial rep because some percentage of his answers get upvoted or accepted.

LOL. Speaking of editing posts, I just noticed that I misspelled the name of this chat room.

Hi Fix, yeah I know. The a-hole really gets under my skin.

Would you test the formula in his answer? It clearly gives the wrong answer, and I think he purposely fudged the screenshot so he could pretend the formula works correctly. He's a liar as well as an a-hole. Notice he added a second screenshot to make it look like the formula was filled all the way down.

@Bandersnatch, his formula does not return a zero for me. But, I think both answers might be trying to solve a different problem than the OP has. His comment says the issue was the OP's $G$37:G42 range, which he fixed by changing the range. $G$37:G42 is where the OP needs the unique list, and that behaves differently than the examples in the answers.

I don't have time to explore this right now, but try replicating the OP's example exactly. Pre-fill the $G$37:G42 range with the OP's formula (apparently looking for a max of 6 results). Then start adding values in col A starting in A5, including some duplicates for verification.

The OP's formula produces a zero when 5 of the 6 unique values have been found and there is a blank within the range.

If you extend the result range by another cell, you get additional interesting behavior. I dabbled a little and my brain isn't awake enough, yet, to deal with it. :-)

@fixer1234 @fixer, The formula has to starting G38, or you get a circular reference. In that cell it's: =IFERROR(INDEX($A$5:$A$30, MATCH(0,COUNTIF($G$37:G37, $A$5:$A$30), 0)),""). If you fill that down, then click in G42, you get the formula OP posted...

...There's nothing wrong with that range, OP just didn't copy the formula in the first cell. Maybe G42 is where his 0 is. I get a zero with his formula and a-hole's. It worries me that you don't. Maybe there's an Excel preference that affects this?

I've extended the result range way down, and only get blanks. What's the "interesting behavior"?

@Bandersnatch, clearly, I haven't had enough coffee yet. When I replicated the OP's example, I started with his G42 cell as listed in the question and used it as my starting point in G37 and replicated from there. If you do that, you get some really interesting behavior. :-) Yeah, starting with $G$37:G37, you immediately get zeros. But that didn't happen when the results started in B2. But now I don't trust what I did before. I'll retest and see what happens.

OK, I just replicated his formula, which references $B$1:$B1 in B2. I copied down a limited range of results cells (through B6). I mixed unique and duplicate values in col A, and blank cells internal to the list and at the end of the list in A. No zeros.

It isn't clear to me exactly what the OP did. There are a lot of possible variations represented by only his single formula in the question.

If he started his formula referencing the starting cell instead of the previous cell, that will produce zeros.

@fixer1234 OK, take a look at this spreadsheet: dropbox.com/s/4q0ikcya4s1rcr8/Formula%20Comparison.xlsx?dl=0

and see if you can find what the difference between it and your formula that gives no zeros.

No rush. I'm calmer now. :-)

This is giving me brain damage. :-) Your formula addresses the underlying problem and works. We don't know what the OP did. Maybe he did what you replicated, maybe something different. So we can't be sure exactly what problem we're solving. The weird part is with our friend's solution.

I'm working in LO Calc. If I open your spreadsheet and manipulate what you've entered, I do get zeros. However, if I copy the formula to a new column, adjust the references, and save it as an array formula, and then copy/paste it down the column, it behaves differently (no zeros).

@Bandersnatch, that's the same behavior I got when I created my own example. The formulas match, but the behavior is different depending on whether I create the formula with Calc or use the existing Excel formula. I'm not sure I have enough coffee to figure this out. :-)

@fixer1234 What's Calc?

Oh, nm. Libre Office?

NM. Another dumb question. You're "working in LO Calc." I think Excel might be required to get the zeros.

@Bandersnatch, right, LO Calc. I don't have ready access to Excel. If I create an example in LO Calc, no zeros with our friend's formula. If I open your worksheet in LO Calc, your entries of our friend's formulas produce zeros. In that worksheet, if I copy the formula you entered and paste a reference-adjusted copy as an array formula, that doesn't produce zeros. It's possible that the difference is just one of the quirky ways LO Calc behaves differently from Excel.

@Bandersnatch, just noticed that you broke the 3K barrier. Congrats.