INDEX & MATCH formula avoiding duplicates

0
votes

I got a first sheet with the name and companies of people, I want to import the company names on a second sheet avoiding duplicates.

I've been using the INDEX & MATCH function but I couldn't make it work.

Here's my formula: =IFERROR(INDEX('Liste de contact'!B$2:B$990;SMALL(IF('Liste de contact'!B$2:B$990<>"";ROW(INDIRECT("1:"&ROWS('Liste de contact'!B$2:B$990))));ROWS($1:1))))

1
excelindexingmatchformula
First, using INDIRECT() is hardly ever a good solution. If you have a recent version of Excel, the UNIQUE() function may do what you want. - DS_London
I tried to use the UNIQUE function but got the same result =UNIQUE(IFERROR(INDEX('Liste de contact'!B$2:B$990;SMALL(IF('Liste de contact'!B$2:B$990<>"";ROW(INDIRECT("1:"&ROWS('Liste de contact'!B$2:B$990))));ROWS($1:1))))) - Antoine Salaun
What happens if you just use =UNIQUE('Liste de contact'!B$2:B$990) in a single cell on the new sheet? UNIQUE() is an array function, so you use it in a single cell and Excel will try and expand the results for you. - DS_London
Well guess what, it worked ! Thanks a lot ! - Antoine Salaun
De rien. Glad you are up and running. - DS_London

1 Answers

1
votes

This is pretty much why the UNIQUE() function was added in Excel (together with SORT() and FILTER()).

enter image description here

Users had been writing their own UDFs, or resorting to the kind of formulae in the question. Microsoft probably had to wait for Excel to better handle array functions I suppose.