Return unique list from a column based on a criteira from another column

Hi all,

I have been searching for help on this for a while but, have drawn a blank.

One on sheet I have a column with data that could be duplicated. In another sheet I want to pull through a unique list (i.e. it only retrieves one of each value) from this column based on if another column contains part of a word (so "Example*")

I have found a couple of formulas in formulas but it either only returns the first value or completes the "iter" function. I am unable to upload an example but, can later if needs be.

Column A...Column B
Test 1........A
Test 2........A
Test 1........A
Test 1........B
Test 1........C
Test 1........C
Test 1........B
Test 2........D

So, if I want to return a list from column B based on the criteria for column A, so if I selected Test 1 as the condition the results would be: -

A
B
C

Sorry if I have not explained that very well but, help would be very much appreciated as I am stuck!

This should work:

Array entered, Ctrl+Shift+Enter instead of regular Enter
=INDEX($B$2:$B$8, MATCH(0, IF($E$2=$A$2:$A$8, COUNTIF($F$1:$F1, $B$2:$B$8), ""), 0))

You can wrap it in a IFERROR to take care of errors

Hi,

I seem to be getting the same errors that I have previously. The condition I am searching for is slightly variable so, rather than it pointing to a specific cell for the condition look up I need to be able to type "Test 1*", will this make any difference?

Untested, try :

=INDEX($B$1:$B$8, SMALL(IF(ISNUMBER(SEARCH($E$2, $A$1:$A$8)), MATCH(ROW($A$1:$A$8), ROW($A$1:$A$8))), ROWS($C$1:C1)))

But the wildcard is unnecessary. Array entered as well.

Hi,

So this formula works brilliantly for the first group of the column but, I could have some values in column A that say for example "Test 1 - Other". These aren't pulling through on the formula?

Hi,

I did make reference to it in the original post: -

"from this column based on if another column contains part of a word (so "Example*")" however I did not use it in the example, I apologise.

Thanks for the update but unfortunately I need it as one list so, the text will always start with Test but could have additional text at the end. Which is why I am trying to use the "Test*" wildcard.

Again thanks for all the help so far.

Hi, thanks for the reply. Formula is working well, it's just the issue around the potential additional text that's stopping it working completely.

I might have to find another way around it!

Thanks again!