## Filter unique distinct values where adjacent cells contain search string

*Article updated on October 06, 2017*

**Question:** How do I create a unique distinct list where adjacent cell values contains a search string?

AA102 CA

AA103 BC

AA104 CA

AD103 SD

AA201 CC

Search string: 1

Unique distinct list

CA

BC

SD

**Answer:**

### Filter unique distinct values where adjacent cells contain search string

Array formula in C12:

copied down as far as needed.

**Named ranges**

Column_txt (C2:C6)

Search_column (B2:B6)

What is named ranges?

### How to implement array formula to your workbook

Change the named ranges. If your list starts at, for example, F3. Change $C$11:C11 to F2:$F$2, in the above formulas.

### Download excel file for this tutorial.

Filter unique distinct values where adjacent cells contain a search string.xls

(Excel 97-2003 Workbook *.xls)

### Functions in this article:

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

**MATCH(**lookup_value;lookup_array; [match_type]

Returns the relative position of an item in an array that matches a specified value

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**SEARCH()** Returns the number of the character at which a specific character or text string is first found, reading left to right (not case sensitive)

Extract a unique distinct list and sum amounts based on a condition

Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]Extract unique distinct values from a multi-column cell range

Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]How to create a unique distinct list where other columns meet two criteria

Question: How do I create a unique distinct list where other columns meet two criteria using excel array formula? Answer: […]Extract unique distinct values from a filtered table [udf and array formula]

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]### 16 Responses to “Filter unique distinct values where adjacent cells contain search string”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

can you do this with multiple search strings?

Arielle,

Yes!

Formula in cell C12:

=INDEX(Column_txt, MATCH(0, COUNTIF($C$11:C11, Column_txt)*(ISNUMBER(SEARCH($C$8, Search_column))+ISNUMBER(SEARCH($C$9, Search_column))), 0)) + CTRL + SHIFT + ENTER

Copy cell C12 and paste it down as far as needed.

The second search string is in cell C9.

Okay I need to do this with text that searches cells with text separated by commas.

ex. search string: fin

search string: pro

search string: crm

cell A1: fin, pro, crm

cell A2: pro, crm

will the formula work for that so that it displays the cell that contains all three strings?

Arielle,

No, try this formula in cell C12:

=INDEX(Column_txt, MATCH(0, COUNTIF($C$11:C11, Column_txt)*(SEARCH($C$8, Search_column)*SEARCH($C$9, Search_column)*SEARCH($C$10, Search_column)), 0)) + CTRL + SHIFT + ENTER

Copy cell C12 and paste it down as far as needed.

The search strings are in cell C8,C9 and C10.

The array formula creates a unique distinct list of all cells containing the value in cell C8,C9 and C10.

Thank you for commenting!

Great it worked!! I changed the search function to find because the program I am using doesn't support the excel search function and it works great!!

Thanks again!!!!!

Arielle,

I am happy it worked!

is there a way to do this without using an array?

well without using the ctrl + shift + enter part. even if I had to break up the formula into 2 or 3 different cells then multiply/add or whatever them together in another cell.

Arielle,

In cell E2:

=(SEARCH($C$8, B2)*SEARCH($C$9, B2))*SEARCH($C$10, B2)) + ENTER. Copy cell E2 and paste it down as far as needed.

In cell F2:

=IF(ISNUMBER(E2)*(MATCH(C2;$C$2:$C$6;0)=ROW()-1);C2;"") + ENTER.

Copy cell F2 and paste it down as far as needed.

The list you get in column F contains a lot of blank rows.

when i evaluate the formula without the crtl shift enter, it fails when it gets to the match function, so maybe there is a way around that without the ctrl shift enter. I included all of the supported functions in xcelsius, which makes it difficult to create a formula to by pass all these issues. I can even use a formula like https://www.get-digital-help.com/2009/08/24/search-and-display-all-cells-that-contain-all-search-strings-in-excel/ to find its location or its cell number and then i can lookup the other value that i really need but without all of the unsupported functions. i really hope you can help!!!

ive used your functions for other projects but i was able to bypass the unsupported functions and the array issue by using a macro that copied the values and pasted them on another section of the worksheet and then referenced to the pasted cells. i cant do that with this project because of this search that you gave me the previous formula for. (so basically the formula you gave me works but i just need to avoid the ctrl shift enter so that everything will display in xcelsius)

ABS ACOS ACOSH ADDRESS AND ASIN

ASINH ASSIGN ATAN ATAN2 ATANH AVEDEV

AVERAGE AVERAGEA AVERAGEIF BETADIST CEILING CHAR

CHOOSE CODE COLUMN COLUMNS COMBIN CONCATENATE

CORREL COS COSH COUNT COUNTA COUNTBLANK

COUNTIF COVAR DATE DATEVALUE DAVERAGE DAY

DAYS360 DB DCOUNT DCOUNTA DDB DEGREES

DEVSQ DGET DIVIDE DMAX DMIN DOLLAR

DPRODUCT DSTDEV DSSTDEVP DSUM DVAR DVARP

EDATE EFFECT EOMONTH EVEN EXACT EXP

EXPONDIST FACT FACTDOUBLE FALSE FIND FISHER

FISHERINV FIXED FLOOR FORECAST FV GE

GEOMEAN GT HARMEAN HLOOKUP HOUR IF

INDEX INDIRECT INT INTERCEPT IPMT

IRR ISBLANK ISEVEN ISLOGICAL

ISNA ISNONTEXT ISNUMBER ISODD ISTEXT KURT

LARGE LE LEFT LEN LN LOG

LOG10 LOOKUP LOWER MATCH MAX MAXA

MEDIAN MID MIN MINA MINUS MINUTE

MIRR MOD MODE MONTH N NE

NETWORKDAYS NORMDIST NORMINV NORMSINV NOT NOW

NPER NPV OFFSET OR PEARSON PERCENTILE

PERCENTRANK PERMUT PI PMT POWER PPMT

PRODUCT PV QUARTILE QUOTIENT RADIANS RAND

RANDBETWEEN RANGE_COLON RANK RATE REPLACE REPT

RIGHT ROUND ROUNDDOWN ROUNDUP

RSQ SECOND SIGN SIN SINH SLN

SLOPE SMALL SQRT STANDARDIZE STDEV STDEVA

STDEVP SUBTOTAL SUM SUMIF SUMPRODUCT SUMSQ

SUMX2MY2 SUMX2PY2 SUMXMY2 SYD TAN TANH

TEXT TIME TIMEVALUE TODAY TRUE TRUNC

TYPE VALUE VAR VARA VARP VARPA

VDB VLOOKUP WEEKDAY WEEKNUM WORKDAY YEAR

YEARFRAC

Ariel,

Do you get an error or is the cell blank in your spreadsheet?

Are the above functions unsupported?

Hi!

sorry about the confusion. I sent you the 2nd message and I didn't realize that you had sent me another formula to try. when i sent you the second message i was talking about the formula you gave me =INDEX(Column_txt, MATCH(0, COUNTIF($C$11:C11, Column_txt)*(SEARCH($C$8, Search_column)*SEARCH($C$9, Search_column)*SEARCH($C$10, Search_column)), 0)) and i did it without the ctrl shift enter and when i evaluated the formula it failed at the match function. when i did the formula you just sent to me In cell E2:

=(SEARCH($C$8, B2)*SEARCH($C$9, B2))*SEARCH($C$10, B2)) + ENTER. Copy cell E2 and paste it down as far as needed.

In cell F2:

=IF(ISNUMBER(E2)*(MATCH(C2;$C$2:$C$6;0)=ROW()-1);C2;"") + ENTER.

Copy cell F2 and paste it down as far as needed.

it gives me a value error in cell E2 and blanks in F2.

all the formulas that i listed above are the supported functions so let me know if there is any way possible to achieve what i want by using those functions.

thanks again for all of your help and im sorry about how confusing i am!

Arielle,

The formula in cell E2 returns an error if any or all of the search strings in C8, C9 and C10 are not found. If all of the search strings are found the formula returns a number.

The formula in cell F2 returns a unique distinct list depending on the outcome of the formula in cell E2.

Don´t forget to copy the formulas down as far as needed.

Hi Oscar! Here is what I have to do and you can tell me if it is possible. I have to search for a cell in a table and then display the column title.

search value in cell e1: AA

table in cells A1:C6

x y z

BB CC DD

AA GG AA

CC EE

FF HH

II

then the values to be displayed from the search would be:

x in one cell and z in the next cell. the display of the search values can either be in a row-(g1: x h1: z) or a column-(g1: x g2: z)

Let me know if this type of search is possible, thanks!

apologies, my table did not display properly-

A1:x B1:y C1:z

A2:BB B2:CC C2:DD

A3:AA B3:GG C3:AA

A4:CC B4:BLANK C4:EE

A5:FF B5:BLANK C5:HH

A6:BLANK B6:BLANK C6:II

Arielle,

see this post: https://www.get-digital-help.com/2010/08/16/search-for-a-cell-in-a-table-and-then-display-the-column-title-in-excel/