Search code examples
excelexcel-formulamatchunique

Why is it not returning the values of INDEX formula?

I'm using Excel 2016 and I'm finding for unique data from an array using INDEX, MATCH, and COUNT formula as follows;

{=INDEX(list,MATCH(0,COUNTIF(uniquelist,list),0))}

list = table[column_name] and uniquelist = $columnRow($B2:B2). I tried putting hard values and the results are the same. My logic is simple; count the list with initial empty cell. That returns {0,0,..} array which gives MATCH(0,{0,0,..},0). That gives first index of matched value. So, I got INDEX(list,1) and it should return the first item, not 0.

I have tried searching on the Internet, and some people use new formula UNIQUE which is available only for Office365. I am getting 0 only and when I try to evaluate my formula, it shows the correct result until the last step.

My formula

My formula with the list

Am I using the wrong formula? I don't want to simply copy&paste with "Remove Duplicates" feature built-in Excel.

Solution

  • https://support.microsoft.com/en-us/office/remove-or-allow-a-circular-reference-8540bd0f-6e97-4483-bcf7-1b49cd50d123#:~:text=If%20this%20confuses%20you%2C%20imagine,the%20formula%20in%20the%20cell.

    Turn on Iterative Calculation

    I HATE it when the application prevents the client's work with just a warning just because it may has impact on performance. Of course, my bad for not reading carefully every time a warning dialog box appears.