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

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

AA102 CA

AA103 BC

AA104 CA

AD103 SD

AA201 CC

Search string: 1

Unique distinct list

CA

BC

SD

The formula in cellC13 extracts unique distinct values from cell range C3:C7 if adjacent value in column B contains 1.

Formula in C13:

copied down as far as needed.

### Explaining formula in cell C13

#### Step 1 - Identify adjacent values containing the search string

The SEARCH function returns a number representing the position of a given string in a cell.

SEARCH($C$9, $B$3:$B$7)

becomes

SEARCH(1, $B$3:$B$7)

becomes

SEARCH(1, {"AA102"; "AA103"; "AA104"; "AD103"; "AA202"})

and returns

{3; 3; 3; 3; #VALUE!}

#### Step 2 - Prevent duplicates

The COUNTIF function counts values based on a condition or criteria, if number is 0 (zero) then value has not yet been displayed. The first argument contains an expanding cell reference, when you copy the cell and paste to cells below the cell reference grows. This will make the formula aware of displayed values above current cell.

COUNTIF($C$12:C12, $C$3:$C$7)=0

becomes

COUNTIF(0,{"CA";"BC";"CA";"SD";"CC"})=0

becomes

{0;0;0;0;0}=0

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE}.

#### Step 3 - Multiply arrays

Use AND logic because both arrays must be TRUE in order to be extracted.

((COUNTIF($C$12:C12, $C$3:$C$7)=0)*SEARCH($C$9, $B$3:$B$7))

becomes

{3; 3; 3; 3; #VALUE!}*{TRUE; TRUE; TRUE; TRUE; TRUE}

and returns

{TRUE; TRUE; TRUE; TRUE; #VALUE!}

#### Step 4 - Divide 1 with array

Divide 1 with array will return a !DIV/0 error if value is zero or FALSE. Any #VALUE! error will continue as is in array, as well.

1/((COUNTIF($C$12:C12, $C$3:$C$7)=0)*SEARCH($C$9, $B$3:$B$7))

becomes

1/{TRUE; TRUE; TRUE; TRUE; #VALUE!}

and returns

{1; 1; 1; 1; #VALUE!}

#### Step 5 - Get value

The LOOKUP function returns values ignoring error values, the COUNTIF function makes sure that unique distinct values are extracted.

LOOKUP(2, 1/((COUNTIF($C$12:C12, $C$3:$C$7)=0)*SEARCH($C$9, $B$3:$B$7)), $C$3:$C$7)

becomes

LOOKUP(2, {1; 1; 1; 1; #VALUE!}, $C$3:$C$7)

and returns "SD" in cell C13.

### Download Excel *.xlsx file

Extract unique distinct values where search string is found in adjacent cells.xlsx

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

Extract a unique distinct list from two columns

Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]

Create a unique distinct alphabetically sorted list

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]

Vlookup – Return multiple unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]

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 a unique distinct list from three columns

Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]

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. […]

Extract a unique distinct list and remove blanks

Question: How do I create a unique distinct list from a list containing several blanks? Answer: Cell range B3:B12 contains several […]

Extract a unique distinct list sorted alphabetically and ignore blanks from a range

This is an answer to a question in this blog post: Extract a unique distinct list sorted from A-Z from […]

How to create a unique distinct list based on two conditions

Question: How do I create a unique distinct list where other columns meet two criteria using excel array formula? Answer: […]

### 16 Responses to “Filter unique distinct values where adjacent cells contain search string”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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/