# Excel 2007 : Retrieving unique values from a list by INDEX,MATCH, COUNTIF

1. ## Retrieving unique values from a list by INDEX,MATCH, COUNTIF

Column B3:B100 contains a list of postcode sectors, which appear more than once. I want to create a list in column C in which each unique postcode in column B appears only once. Previously I have sorted copied and pasted or used a pivot table but am trying to sort column C out with a formula. I have tried to use a INDEX, MATCH, COUNTIF formula (see sample) but it is not working! Am using in cell C4 ( leaving the first cell in the unique list empty) =INDEX(\$B\$3:\$B\$100,MATCH(0,COUNTIF(\$C\$3:C3,\$B\$3:\$B\$100),0)) but am returning #N/A. which should appear when the list of unique values has completed in Column C.

2. ## Re: Retrieving unique values from a list by INDEX,MATCH, COUNTIF

Hi Mikey.

This is an Array formula and needs to confirm with C0ntrol+Shift+Enter. Not just Enter.

Also put an IFERROR in front of the formula to do not gives you #NA...

IFERROR(INDEX(\$B\$3:\$B\$100,MATCH(0,COUNTIF(\$C\$3:C3,\$B\$3:\$B\$100),0)),"")

3. ## Re: Retrieving unique values from a list by INDEX,MATCH, COUNTIF

Here is also another solution (non-array):

4. ## Re: Retrieving unique values from a list by INDEX,MATCH, COUNTIF

Hi Fortis, my first attempt, thanks for your response and solution.
Mikey

5. ## Re: Retrieving unique values from a list by INDEX,MATCH, COUNTIF

Thanks for quick response and showing both solutions,
Rgds
Mikey

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1