As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread:

Excel newbie stymied

urbanvoleurbanvole Registered User new member
I am trying to create a list of unique values from an entire column in another sheet. To do so I am using the following function:

=IFERROR(INDEX(Export!$M$2:$M$60000, MATCH(0, IF(ISBLANK(Export!$M$2:$M$60000), 1, COUNTIF($A$1:A1, Export!$M$2:$M$60000)), 0)), "")

When activated, however, the function returns the first value over and over. I'm not sure what I am doing incorrectly - although I'm sure it is something fairly basic and obvious - and would appreciate any assistance anyone might be able to offer!

Thanks in advance,


  • urbanvoleurbanvole Registered User new member
    NM - It randomly works now. Don't know why, but I'll take it! Onwards!

  • CauldCauld Registered User regular
    why not copy and paste the list and then do "remove duplicates"?

  • PailryderPailryder Registered User regular
    or safer would be to add a column that shows if a value is a duplicate or not by counting a specific value and if it sees only one then you can build off a list of those safe values.

  • SerpentSerpent Sometimes Vancouver, BC, sometimes Brisbane, QLDRegistered User regular
    Or you could pivot it and put that column in the 'rows' field of the pivot. Or you could use the advanced filter and filter to unique values. Or you could use Power Query and remove duplicates. There's about 1000 ways to do this and most of them are easier than your formula.

    However, the fact you could build up that formula itself implies you're not an excel newbie. Give yourself some credit! Index/Match itself is 'advanced' as it shows you've moved beyond vlookup...

  • useless4useless4 Registered User regular
    Yea dump your data into a table, make a pivot off of it and remind everyone to hit refresh data every time they update the table.

  • PailryderPailryder Registered User regular
    hey now, never underestimate the power of vlookup! :)

    Rhesus Positivemysticjuicer
Sign In or Register to comment.