Look up multiple results but ignore duplicates

**aaronfi** · 10-21-2015, 08:31 AM

Hello,

First time poster here. I am hoping someone can help me with adding to or modifying a formula. I currently have a formula that builds a list of items for me that fall under a certain criteria, but includes duplicates. I am looking to see if I can add to this formula, so that duplicate values do not show up.

Here's my current formula (you will see I am referencing 2 tabs in a single Excel file):
=INDEX('Product Roadmap'!$B$7:$B$1107, SMALL(IF('Component Roadmap'!$A$3='Product Roadmap'!$A$7:$A$1107, ROW('Product Roadmap'!$A$7:$A$1107)-ROW('Product Roadmap'!$A$7)+1), ROW(1:1)))

So instead of the list ending up looking like:

apples
bananas
peaches
bananas

I want the final list to look like:
apples
bananas
peaches

Hope I provided enough info!

**Glenn Kennedy** · 10-21-2015, 08:46 AM

Yep. That's clear. However, it would be much easier to test if you were to upload a small sample file, showing what you need. Remember to remove ALL confidential information first!!!

Click on GO ADVANCED and use the paperclip icon to open the upload window.

The paperclip icon

**Glenn Kennedy** · 10-21-2015, 08:56 AM

OK. I had a go at it, using this array formula.

Formula:

Please Login or Register  to view this content.

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

take a look and let me know....

**aaronfi** · 10-21-2015, 09:40 AM

Glenn, THANK YOU for the quick response! Your sample was perfect, and it allowed me to pull the formula into my existing spreadsheet with only a couple tweaks. I really appreciate the help!