# VLOOKUP – Return multiple unique distinct values

This article shows how to extract unique distinct values based on a condition applied to an adjacent column using formulas.

#### Table of Contents

- VLOOKUP - Return multiple unique distinct values
- VLOOKUP - Return multiple unique distinct values (Excel 365)
- Create a unique distinct list based on criteria
- Filter unique distinct values where adjacent cells contain search string
- Extract unique distinct values from cell range that begins with string
- Extract unique distinct text values containing string in a range
- Extract unique distinct values if adjacent cell is text

## 1. VLOOKUP - Return multiple unique distinct values

How to return multiple values using VLOOKUP in Excel and removing duplicates?

I have tried the formula to return multiple values using the index example and worked fine with no duplicate item but how can I list them without the duplicate?

#### Answer:

The following array formula is easier to understand than a VLOOKUP formula.

*Update, 2017-08-16! New smaller regular formula.*

Formula in cell G3:

Array formula in cell G3:

The formulas above do not sort the unique distinct list.

### 1.1 Watch I video where I explain the formula above

Recommended articles

Recommended articles

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

Recommended articles

This post explains how to lookup a value and return multiple values. No array formula required.

Recommended articles

This article demonstrates how to extract multiple values based on a search value and display them sorted from A to […]

### 1.2 How to enter an array formula

- Double press with left mouse button on cell G3
- Copy (Ctrl + c) and paste (Ctrl + v) array formula to cell
- Press and hold Ctrl + Shift simulatenously
- Press Enter once
- Release all keys

Recommended articles

Array formulas allows you to do advanced calculations not possible with regular formulas.

### 1.3 Copy array formula

- Select cell G3
- Copy cell ( Ctrl + c)
- Select cell range G4:10
- Paste (Ctrl - v)

### 1.4 Explaining array formula in cell E8

#### Step 1 - Compare cell value in E3 with column Category and return a boolean array

The less than and larger than characters are logical operators, they return boolean value TRUE if the condition is met and FALSE if not.

$B$3:$B$10<>$E$3

returns {FALSE; TRUE; FALSE; ... ; TRUE}

#### Step 2 - Check earlier values above the current cell

The COUNTIF function calculates the number of cells that is equal to a condition.

Function syntax: COUNTIF(range, criteria)

COUNTIF($G$2:G2,$C$3:$C$10)

returns {0;0;0;... ;0}.

#### Step 3 - Add arrays

The plus sign lets you add numbers in an Excel formula. You can use the plus sign to create OR logic between boolean values or their numerical equivalents.

TRUE - any number except 0 (zero)

FALSE - 0 (zero)

COUNTIF($G$2:G2,$C$3:$C$10)+($B$3:$B$10<>$E$3)

becomes

{FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE} +Â {0;0;0;0;0;0;0;0}

and returns

{0;1;0;0;0;0;0;1}.

TRUE + TRUE = TRUE

TRUE + FALSE = TRUE

FALSE + FALSE = FALSE

#### Step 4 - Match first zero value in array and return relative position

The MATCH function returns the relative position of an item in an array that matchesÂ a specified value in a specific order.

Function syntax: MATCH(lookup_value, lookup_array, [match_type])

MATCH(0,COUNTIF($G$2:G2,$C$3:$C$10)+($B$3:$B$10<>$E$3),0)

becomes

MATCH(0,{0;1;0;0;0;0;0;1},0)

and returns 1.

#### Step 5 - Return a value or reference of the cell at the intersection of a particular row and column

The INDEX function returns a value or reference from a cell range or array, you specify which value based on a row and column number.

Function syntax: INDEX(array, [row_num], [column_num])

INDEX($C$3:$C$10,MATCH(0,COUNTIF($G$2:G2,$C$3:$C$10)+($B$3:$B$10<>$E$3),0))

and returns "A" in cell G3.

Recommended articles

Gets a value in a specific cell range based on a row and column number.

### 1.5 Get excel file

Vlookup - Return multiple unique distinct valuesv2.xlsx

(Excel 97-2003 Workbook *.xls)

## 2. VLOOKUP - Return multiple unique distinct values (Excel 365)

Excel 365 dynamic array formula in cell G3:

### 2.1 Explaining formula

#### Step 1 - Logical expression

The equal sign is a logical operator, it compares value to value. It also works with value to multiple values. The result is a boolean value TRUE or FALSE.

E3=B3:B10

returns {TRUE; FALSE; TRUE; ... ; FALSE}.

#### Step 2 - Filter values

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER(C3:C10,E3=B3:B10)

returns {"A";"A";"A";"B";"B";"C"}.

#### Step 3 - Unique distinct values

The UNIQUE function returns a unique or unique distinct list.

Function syntax: UNIQUE(array,[by_col],[exactly_once])

UNIQUE(FILTER(C3:C10,E3=B3:B10))

returns {"A";"B";"C"}.

## 3. Create a unique distinct list based on criteria

The regular formula in cell E6 extracts unique distinct values from column B based on the corresponding number in column C, the min value is in cell F2 and the Max value is in cell F3.

Formula in cell E6:

### Explaining formula in cell E6

#### Step 1 - Prevent duplicate values

The COUNTIF function counts values based on a condition, in this case, multiple conditions. The first argument has an expanding cell range that grows when the cell is copied to cells below. This allows the formula to count for previously shown values.

COUNTIF($E$5:E5, $B$3:$B$12)=0

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

#### Step 2 - Check which records meet criteria

The less than and greater than signs lets you create logical expressions. Each condition is checked against the values in column C.

($C$3:$C$12<=$F$3)*($C$3:$C$12>=$F$2)

returns {1;0;0;... ;1}

#### Step 3 - Multiply arrays

All criteria must be met.

(COUNTIF($E$5:E5,$B$3:$B$12)=0)*($C$3:$C$12<=$F$3)*($C$3:$C$12>=$F$2)

returns {1;0;0;... ;1}

#### Step 4 - Divide 1 with array

The LOOKUP function ignores errors, if we divide something with 0 (zero) we get #DIV/0! error.

1/((COUNTIF($E$5:E5,$B$3:$B$12)=0)*($C$3:$C$12<=$F$3)*($C$3:$C$12>=$F$2))

returns {1;#DIV/0!;... ;1}.

#### Step 5 - Return value

LOOKUP(2, 1/((COUNTIF($E$5:E5, $B$3:$B$12)=0)*($C$3:$C$12<=$F$3)*($C$3:$C$12>=$F$2)), $B$3:$B$12)

returns "AA"

### Get Excel *.xlsx file

unique-list-with-criteria_1v2.xlsx

## 4. 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.

Excel 365 dynamic array formula in cell C13:

### 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)

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

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))

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))

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)

returns "SD" in cell C13.

### Get Excel *.xlsxÂ file

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

## 5. Extract unique distinct values from cell range that begins with string

The array formula in cell B10 extracts unique distinct values from cell range B2:D4 that begins with a given condition specified in cell C7.

Excel 365 dynamic array formula in cell B10:

Array formula in B10:

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

### Explaining formula in cell B10

#### Step 1 - Identify values beginning with search string

The LEFT function returns a given number of characters from the start of a text string.

LEFT($B$2:$D$4, LEN($C$7))=$C$7

returns {FALSE,FALSE,TRUE; ... ,FALSE}.

#### Step 2 - Keep track of previous values

TheÂ COUNTIF functionÂ counts values based on a condition or criteria, the first argument contains an expanding cell reference, it grows when the cell is copied to cells below. This makes the formula aware of values displayed in cells above.

COUNTIF(B9:$B$9, $B$2:$D$4)=0

returns {TRUE,TRUE,TRUE; ... ,TRUE}.

#### Step 3 - Multiply arrays

Both values must be true in order to get the value in a later step.

(LEFT($B$2:$D$4, LEN($C$7))=$C$7)*(COUNTIF(B9:$B$9, $B$2:$D$4)=0)

returns {0,0,1; ... ,0}

#### Step 4 - Replace TRUE with unique number

TheÂ IF functionÂ returns a unique number if boolean value is TRUE. FALSE returns "" (nothing). The unique number is needed to find the right value in a later step.

IF((LEFT($B$2:$D$4, LEN($C$7))=$C$7)*(COUNTIF(B9:$B$9, $B$2:$D$4)=0), (ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1, "")

returns {"","",2.2;... ,""}

#### Step 5 - Find smallest value in array

TheÂ MIN functionÂ returns the smallest number in array ignoring blanks and text values.

MIN(IF((LEFT($B$2:$D$4,LEN($C$7))=$C$7)*(COUNTIF(B9:$B$9, $B$2:$D$4)=0), (ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1, ""))

returnsÂ 2.2.

#### Step 6 - Find corresponding value

IF(MIN(IF((LEFT($B$2:$D$4, LEN($C$7))=$C$7)*(COUNTIF(B9:$B$9, $B$2:$D$4)=0), (ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1, ""))=(ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1, $B$2:$D$4, "")

returns {"","","Blackberry";"","","";"","",""}

#### Step 7 - Concatenate strings in array

TheÂ TEXTJOIN functionÂ returns values concatenated ignoring blanks in array.

TEXTJOIN("", TRUE, IF(MIN(IF((LEFT($B$2:$D$4, LEN($C$7))=$C$7)*(COUNTIF(B9:$B$9, $B$2:$D$4)=0), (ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1, ""))=(ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1, $B$2:$D$4, ""))

returns "Blackberry" in cell B10.

### Get Excel *.xlsx file

Extract unique distinct values begins with A in a range using array formula in excel.xlsx

## 6. Extract unique distinct text values containing string in a range

The formula in cell B10 extracts unique distinct values from cell range B2:d4 that contains the string specified in cell C7.

Excel 365 dynamic array formula in cell B10:

Array formula in B10:

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with aÂ beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### Explaining formula in cell B10

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

TheÂ SEARCH functionÂ returns a number representing the location of a text string in another string.

ISNUMBER(SEARCH($C$7,$B$2:$D$4))

returns {TRUE,TRUE, TRUE;... , TRUE}

#### Step 2 - Keep track of previous values

TheÂ COUNTIF functionÂ counts values based on a condition or criteria, the first argument contains an expanding cell reference, it grows when the cell is copied to cells below. This makes the formula aware of values displayed in cells above. 0 (zero) indicates values that not yet have been displayed

COUNTIF(B9:$B$9,$B$2:$D$4)=0

returns {TRUE,TRUE,TRUE; ... ,TRUE}.

#### Step 3 - Multiply arrays

Both values must be true in order to get the value in a later step.

(COUNTIF(B9:$B$9,$B$2:$D$4)=0)*ISNUMBER(SEARCH($C$7,$B$2:$D$4))

returns {1,1,1;..., 1}

#### Step 4 - Replace TRUE with unique number

TheÂ IF functionÂ returns a unique number if boolean value is TRUE. FALSE returns "" (nothing). The unique number is needed to find the right value in a later step.

IF((COUNTIF(B9:$B$9,$B$2:$D$4)=0)*ISNUMBER(SEARCH($C$7,$B$2:$D$4)),(ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1,"")

returns {2.33333333333333, 2.25,2.2; .... , 4.2}

#### Step 5 - Find smallest value in array

TheÂ MIN functionÂ returns the smallest number in array ignoring blanks and text values.

MIN(IF((COUNTIF(B9:$B$9,$B$2:$D$4)=0)*ISNUMBER(SEARCH($C$7,$B$2:$D$4)),(ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1,""))

returns 2.2.

#### Step 6 - Find corresponding value

IF(MIN(IF((COUNTIF(B9:$B$9,$B$2:$D$4)=0)*ISNUMBER(SEARCH($C$7,$B$2:$D$4)),(ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1,""))=(ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1,$B$2:$D$4,"")

returns {"","","Blackberry";"","","";"","",""}

#### Step 7 - Concatenate strings in array

TheÂ TEXTJOIN functionÂ returns values concatenated ignoring blanks in array.

TEXTJOIN("",TRUE,IF(MIN(IF((COUNTIF(B9:$B$9,$B$2:$D$4)=0)*ISNUMBER(SEARCH($C$7,$B$2:$D$4)),(ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1,""))=(ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1,$B$2:$D$4,""))

returns "Blackberry" in cell B10.

### Get Excel *.xlsx

Filter unique distinct text values containing string in a range.xlsx

## 7. Extract unique distinct values if adjacent cell is text

**Question:**I want a unique distinct list to be created from a column where an adjacent column has text cell value on the same row?

Array formula in D3:

Excel 365 dynamic array formula in cell D3:

### How to create an array formula

- Copy array formula (Ctrl + c)
- Double press with left mouse button on cell D2
- Paste array formula (Ctrl + v)
- Press and hold Ctrl + Shift
- Press Enter

Recommended article

Recommended articles

Array formulas allows you to do advanced calculations not possible with regular formulas.

#### Explaining formula in cell D3

**Step 1 - Check if adjacent values are not text values**

The ISTEXT function returns boolean value TRUE if a cell contains a text value and FALSE if not. I am using a cell range as an argument so the function returns an array of bollean values.

They correspond to the input values based on position. The image below shows the array from ISTEXT($B$2:$B$17)

We need to know which values are not text values, the array is compared to boolean value FALSE. This will change TRUE to FALSE and FALSE top TRUE, the array becomes:

The image below shows the array from ISTEXT($B$2:$B$17)=FALSE

Excel handles FALSE as the same as 0 (zero) and TRUE as 1 or more.

**Step 2 - Check if value is unique**

The COUNTIF function counts values based on a condition or multiple conditions. It has two arguments *range* and *criteria*. The first argument is a cell reference that grows when cell is copied to cell below.

It contains a cell reference with two parts, the first one is absolute and the second is a relative cell reference. The COUNTIF function is most often used to return a single value, however, in this case, we need it to return an array of values that match the relative positions of the cell values.

COUNTIF($D$1:D1, $A$2:$A$17)

No values have been displayed yet so the array contains 0 (zeros) , if a value had been 1 or more it would have indicated that the value has been displayed in a cell above the current cell.

The image below shows the array in column D.

The image below shows the array in column DÂ from COUNTIF($D$1:D1, $A$2:$A$17)+(ISTEXT($B$2:$B$17)=FALSE)

This array tells us which values that have not yet been displayed and where the adjacent value is a text value.

Recommended articles

Counts the number of cells that meet a specific condition.

**Step 3 - Find relative position**

The MATCH function identifies the position of the first cell value that has not yet been shown and where the adjacent value is a text value.

It matches an exact match meaning a value that is exactly equal to 0 (zer0), this is determined by the third argument.

MATCH(0, COUNTIF($D$1:D1, $A$2:$A$17)+(ISTEXT($B$2:$B$17)=FALSE), 0)

returns 1.

The MATCH function has identified the first value in the array as a unique distyinct value that has an adjacent cell value that contains a text value.

Recommended articles

Identify the position of a value in an array.

**Step 4 - Return value**

The INDEX function simply returns a value from a cell range based on a row and column coordinate. The column coordinate is not neccessary since this example has all values in a single column.

INDEX($A$2:$A$17, MATCH(0, COUNTIF($D$1:D1, $A$2:$A$17)+(ISTEXT($B$2:$B$17)=FALSE), 0))

returns 12 in cell D3.

Recommended articles

Gets a value in a specific cell range based on a row and column number.

### Get the Excel file

unique-list-to-be-created-from-a-column-where-an-adjacent-column-has-text-cell-values3.xlsx

### Unique distinct values category

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

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

Table of Contents Extract a unique distinct list sorted from A to Z Unique distinct list sorted alphabetically based on […]

### Excel categories

### 85 Responses to “VLOOKUP – Return multiple unique distinct values”

### 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

Paste image link to your comment.

**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/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/

thanks a million you really made my day it has been driving me nuts. and thanks for the named range tip. I use it all most of the time and it made it more easy to use.

Regards.

one more question please.

what if the search result is in named range Item

A B C D E

1 Section Category item flavor size

2 food Coffee Espresso none Single

3 food Coffee Espresso none double

4 food Coffee Americano none Single

5 food Coffee Americano none double

Search result to appear in Item Name range

U V

1 Category (Data Validation list) Item (Name range Header )

2 =INDEX(Item, SMALL(IF(($U$1=Category)*(COUNTIF($V$1:V1,Item)=0), ROW(Category)-MIN(ROW(Category))+1, ""), 1)) Returns an error because it doesn't count named range header

Thanks

Ahmed Ali,

I am not sure I understand, can you explain in greater detail?

What is named range Header containing?

What is in cell U1? A data validation list with named range Category or named range Item or named range Header?

thanks for your reply,

U1 is only a data validation in V2 there is named range Header(Item). in V3:V9 (named range Item).

Item

________

None_RECP

Cleaning

Condments

Marketing

Packing

Stationary

I left a blank above header (V1)in order to Count $V$1:V1 it counts one less because "item header"

Ahmed Ali,

I am not sure I understand.

Send an example file without sensitive information using the contact form on this page.

Oscar,it there a way to do an "or" statement. What if you trying to find tea and coffee in the list?

INDEX(Item, SMALL(IF(($C$10=Category)+($C$11=Category)*((COUNTIF($E$9:E9,Item)=0)), ROW(Category)-MIN(ROW(Category))+1, ""), 1))

c11 is tea. I think that countif cannot work with or statements.

Sean,

your array formula works if you add a parantheses to your criteria:

(($C$10=Category)+($C$11=Category))*((COUNTIF($E$9:E9,Item)=0)), ROW(Category)-MIN(ROW(Category))+1, ""), 1))The above array formula grows quickly if we add many critera, this array formula is smaller:

COUNTIF($C$10:$C$11,Category)*(COUNTIF($E$9:E9,Item)=0), ROW(Category)-MIN(ROW(Category))+1, ""), 1))Oscar, It does not work as intended. If Tea and Coffee has Expresso,it will only return Expresso once and not twice. I am looking for a unique distinct list based on each condition. I remember reading that Excel has difficulty with these type of or conditions in arrays.

Sean,

read this post: Filter unique distinct records with a condition in excel 2007

Amazingly useful stuff.

This solution saved me atleast 2 days of what would have otherwise been a manual job!

You're a saviour Oscar!

Thanks

Sugato,

Thanks!

Hi Oscar,

Thanks for this tutorial! I keep running into one problem, though. When I copy and paste the formula into the cell below the original, they both display the same value, even though they should return 2 different values. Where am I going wrong with this? It must be a simple copy/paste mistake but I'm not sure how to resolve it.

Thanks,

E

Thanks so much for posting this! This was exactly what I was looking for.

I ended up using an excel table and using the column data references based off that ("tableData[ColumnName]"), but other than that it worked like a charm.

EH,

Did you copy the cell, not the formula? The formula uses relative cell references, they change when you copy the cell. If you copy the formula they donÂ´t change.

1. Select the cell

2. Copy (Ctrl + c)

3. Select the cell range

4. Paste (Ctrl + v)

When I try to put the distinct list on another sheet and copy down, it will not work. In the sample there are {} that are at the beginning and the end, but when I copy and past and enter the sheet reference, those brackets go away, and the wheels fall off the bus. Can't figure out how to make it work.

Kevin,

thanks for bringing this post to my attention!

I have simplified the array formula and you will also find instructions on how to create an array formula.

I am having difficulty getting this to work properly for my application. I was wondering if anyone would be able to provide me with their two sense. My scenario is as follows:

I have a multiple (8) page workbook. I have to enter a name on the first page. From this input, I would like it to look up this value against a range on the third page (column A), and return all the values from column B that are corresponding matches. I would like these values returned vertically on the 8th page in order to concatenate further data, but I have no problems with that part.

Any insight provided is greatly appreciated.

Hi,

I have a question about that, I need to match 2 columns containing a long description and I want find any word in common between both list and return all lines that match for each line.

for example

List 1 :

ACHARD ELECTRIQUE INC.

ACIER LEROUX (EXP)

ACIER PICARD

ACIER VANGUARD LTEE

ACIER VICTORIA LTEE

List 2

AGF ACIER D ARMATURE

ACIER INTAC LTÃ‰E HIGH STRENGTH PLATES & PROFILE

ACIERS SSAB SUÃ‰DOIS LTÃ‰E

ACIER PICARD

ACIER TAG RIVE-NORD

ACIER LEROUX BOUCHERVILLE

ACIER CENTURY INC.

ACIER MCM

I need to have for each line from the LIST 1 to have all result that contain any word from the LIST 2

Thanks for your help

Hi Oscar,

Thanks, this link has lead me down the right path!

The formula I have at the moment entered into cell H11 and copied down is:

{=INDEX(Data!$P$2:$P$4866, SMALL(IF(($A$11=Data!$G$2:Data!$G$4866)*(COUNTIF($H$10:H10, Data!$P$2:$P$4866)=0), ROW(Data!$G$2:Data!$G$4866)-MIN(ROW(Data!$G$2:Data!$G$4866))+1, ""), 1))}

[\code]

Would probably make it easier to read if i used a named range...but how would i change this so that it lists the values it finds horizontally?

I only have a faint clue of how this works and have played with it trying to get it to work like ti does when copying it down...

Hi Oscar,

Thanks, this link has lead me down the right path!

The formula I have at the moment entered into cell H11 and copied down is:

{=INDEX(Data!$P$2:$P$4866, SMALL(IF(($A$11=Data!$G$2:Data!$G$4866)*(COUNTIF($H$10:H10, Data!$P$2:$P$4866)=0), ROW(Data!$G$2:Data!$G$4866)-MIN(ROW(Data!$G$2:Data!$G$4866))+1, ""), 1))}

[\vb]

Would probably make it easier to read if i used a named range...but how would i change this so that it lists the values it finds horizontally?

I only have a faint clue of how this works and have played with it trying to get it to work like ti does when copying it down...

Michael,

You donÂ´t have to change anything except the absolute/relative cell reference, bolded:

=INDEX(Data!$P$2:$P$4866, SMALL(IF(($A$11=Data!$G$2:Data!$G$4866)*(COUNTIF(

$H$10:H10, Data!$P$2:$P$4866)=0), ROW(Data!$G$2:Data!$G$4866)-MIN(ROW(Data!$G$2:Data!$G$4866))+1, ""), 1))Example, array formula in cell H11:

=INDEX(Data!$P$2:$P$4866, SMALL(IF(($A$11=Data!$G$2:Data!$G$4866)*(COUNTIF(

$G$11:G11, Data!$P$2:$P$4866)=0), ROW(Data!$G$2:Data!$G$4866)-MIN(ROW(Data!$G$2:Data!$G$4866))+1, ""), 1))Thanks Oscar! Works perfectly now, thank you very much.

Oscar,

This is perfect for what I'm looking for. One question for you. What if i wanted to add a column for the date and wanted a list in between certain dates? Would i add on to the If statement? Thanks in advance!

Array formula:

See attached file:

CaseyJB.xls

Perfect! Thank you so much for your help!

I was working with values in a format like 10 x 20 = 200 , in twenty five values at one time only 5 to 8 values were present others were zero, it helped me a lot ,i extracted required result using = condition now i will have only required result in my sheet.

Thank u very much,God Bless you

Sir

Im having two excel files.In first file there are ID(column1),data1(column2),data2(column3),..data12(column12)...In second file Im having only ID(column1)(these ID will be present in first file).Now i want to extract the datas for these ID's from the first file.Please help me to get through this problem....because the file size also bigger it is tough to use ctrl+F...

harinishree,

Use the array formula provided here:

https://www.get-digital-help.com/how-to-return-multiple-values-using-vlookup-in-excel/#multiple

I modified the data a little bit so that we have col A as it is and Col B has all "Espresso". Col C was modified to have 4 different flavor names. I defined the name range for Flavor and modified the formula as below:

=INDEX(Flavor, SMALL(IF(($C$10=Item)*(COUNTIF($E$9:E9,Flavor)=0), ROW(Item)-MIN(ROW(Item))+1, ""), 1))

The above formula does not work and gives #value error. Do you know why it would be so?

sorry, figured out.

Thank you Oscar, the formula provided helped me a lot in automating a template. I am now stuck in a scenario where the data appears like below:

Col B Col C Col D Status Reason

------ ----------- ------ ------- ----------

proj1 description name1 yellow delayed

proj2 description name2 Green on time

proj2 description (blank) Red not on time1

proj2 description (blank) Red not on time2

How do i pull col C, Col D, Col E, Status and Reason accurately. At this moment, I am totally lost. Any assistance that you will provide will be much appreciated.

in other words, how do return multiple both, unique and duplicate values?

HArp,

Read my comment:

https://www.get-digital-help.com/how-to-return-multiple-values-using-vlookup-in-excel/#comment-52791

HI Oscar,

I have a large table filled with various different values. Is there a way to search and return the types of different values in the table?

Vas,

Can you provide some example data and desired outcome?

Hi Oscar,

The formula has worked perfectly :

=INDEX(Item, SMALL(IF(($C$8=Category)*(COUNTIF($E$7:E7, Item)=0), ROW(Category)-MIN(ROW(Category))+1, ""), 1))

However, can you help me to figure out how to Return multiple unique distinct values in excel Horizontally?

I tried to modify the above formula to become "...,""),Column(A1)))}", but it didn't work at all :(

Thanks a lot in advance for your help.

[...] then copy it across and down. This solution was taken from here and slightly altered to suit...... Unique list to be created from a column where an adjacent column has text cell values | Get Digital ... I hope that helps. Good luck. [...]

Why is my formula returning 2 of every unique Value?

=INDEX(JobCode,SMALL(IF(($I$3=Dept)*(COUNTIF($K$1:K1,JobCode)=0),ROW(Dept)-MIN(ROW(Dept))+1,""),1))

Results

20000 Staff Nurse

20000 Staff Nurse

20005 Critical Care Staff Nurse

20005 Critical Care Staff Nurse

20015 Critical Care Staff Nurse II

20015 Critical Care Staff Nurse II

Erin,

Is the array formula entered in cell K2?

Jobcode and Dept are pointing to what cell ranges?

Hello Oscar,

I am working to create a "dynamic" Ingredient calculator. This will use a data validation drop-down to lookup an ingredient in a table with five columns "Ingredient, Formulation, Grams per Unit, % of Unit, and Client". Ingredients will be repeated in column A as they are in multiple Formulations (column b), but I want to return all the possible formulations that contain one ingredient.

I have successfully used INDEX and MATCH to return a (repeating) list based on the value in the data validation.[=(INDEX(Lookup,MATCH($D$3,Ingredients),0))] combined with [=CONTINUE(D13, 1, 2)] across, to return the other column results.

However, even using your formula (with my named ranges), I am getting a list of duplicates, just like my simpler formula above. [=INDEX(Formulation, SMALL(IF(($D$3=Ingredients)*(COUNTIF($D$9:D13, Formulation)=0), ROW(Ingredients)-MIN(ROW(Ingredients))+1, ""), 1))]. Have I messed up your formula?

Just fyi, the named ranges refer to Formulation (column B in 5 column list) and Ingredients (Column A in 5 column list).

Any ideas on what I am doing wrong here? I am at my wit's end here, and could use some help....

ChocolateGal,

Get the Excel file:

ChocolateGal.xlsx

Hi,

I'm trying to create a unique distinct list from a column where an adjacent column has a specific name. I've looked at your guide where an adjacent column meets criteria, but this doesn't seem to help, I've simply replaced the

=INDEX(List_category, MATCH(0, COUNTIF($F$4:F4, List_category)+(List_value$E$2), 0))

With

=INDEX(List_category, MATCH(0, COUNTIF($F$4:F4, List_category)+(List_value="name"), 0))

where the name is actually a link to a cell with the name I'm looking for (format: $A$1)

This hasn't seemed to work for some reason, so I'm a bit stumped.

Could you offer any advice?

Thanks

Harry

[…] Also for reference... this is where I found the formula: Vlookup – Return multiple unique distinct values in excel | Get Digital Help - Microsoft Excel… […]

Hi Oscar, wanna ask, I try your formula for unique values, but it doesnt work if my table for somehow have blank cells, how to tweak it? Thanks for assistance

how would you return your results horizontally instead of vertically?

Hi Oscar, thanks for the tips.

I would like to ask question.

https://s23.postimg.org/9vc5msucb/Untitled_121.jpg

Thanks for your assitance.

Hi, can you explain the countif portion of the string? I understood it as an empty space above the resulting cell e8? Total noob here. Thanks man!

John

It makes sure that only unique distinct values are extracted from column E.

It canÂ´t include a cell ref to the cell itself, that will return a circular reference. So it starts with the cell above, in this case a blank cell (E7).

See step 2 in the explanation above.

Hey, Thanks for this tutorial I was wondering if you can help me tweak the formula to columns rather then rows. In other Words rather than the results being shown on E10,E11,E12,etc I want them on E10, F10, G10, etc.

Thank you

Oscar,

Your code has aided me tremendously; THANK YOU!! I have the existing criterion based, unique distinct list formula

I am trying to add "begins with" criteria to rngITEMDescr, such that any item that begins with "PLATE" is excluded from the unique distinct list.

Randy

Randy Braddock

Thank you.

I have added content to this post:

https://www.get-digital-help.com/automatically-filter-unique-row-records-from-multiple-columns/#notbegin

I believe it has the answer to your question.

Hi Oscar. I see you are quite the excel guru. I am struggling to come up with a solution.

I have a sheet of data with lots of columns. I need to make a template with a certain selection of columns and the source of the data must be based on the week. Like what was produced on week 1. Please help.

i have a qury regarding this, the formula works perfactly but i want to write data from not only one coloumn, i want to lookup for different coloums and give unique ansers as

A B A B c D E F

FR H1 DN 11704

FR D2 DNA 11702

FR H1 DNB 6625

FR D2 DNC 11702 DN300 840 DN20 200

FR H1 DND 10329 DN300 840

i have thess coloums and having criteria for A&B then want answer like all DN values only for FR&D1, comparing all tjhe couloums. How to do this please will you elaborate?

Hello , i cannot understand how americano appears in E9 . i did it in my excel but it cannot appear the rest values. Any advice ?

MYSELF NARESH

I have persons name list with birthdates. There are many dates are repited but name is different in front of that list like

Date Name

12/05/2018 Sanju

10/05/2018 Raju

05/05/2018 Ram

10/05/2018 Sham

05/05/2018 Anju

20/05/2018 Gaju

05/05/2018 Manju

So i want to that first list the date from lowest to highest in one column. And name of persons in front of that date in next column.

how can i do it in excle.

please help me on the same.

Oscar,

I'm attempting to sum values based on multiple criteria but ignoring redundancy. A screenshot of the representative data is below:

https://i.postimg.cc/J0V9dkzK/extract-with-multiple-conditions.jpg

I'm trying to sum the Gaze Event Duration for each value of Ps Name, Fixation Index, and Gaze Event Type while ignoring the redundant values. For example, I need to add the Gaze Event Duration in cell H4 (for Ps001 and Fixation Index 1 while ignoring H5-H9)to the value in cell H14 (for Ps001 and Fixation Index 2 while ignoring H15-H22) etc...

I've tried various vLookup, Index/Match, SumIf, etc... functions in excel but I've hit a wall. Can you help?

Andrew

Andrew,

I recommend using a pivot table, divide the sum with the count and then sum all values.

https://i.postimg.cc/HLs80J7G/andrew-pivot-table.png

Hi Oscar

I have used the formula to show which people work each day of the week, I have a single cell for criteria as in your example and can change it to alter the results.

There should only be five people each day so I dragged the formula into five cells for the results.

My problem is that on some days (e.g. tuesday) there are only four names returned.

That outcome is correct, but the fifth results cell is showing as #N/A, is there a way for it to return as a blank?

Mike,

I recommend the IFNA function:

https://www.get-digital-help.com/2018/01/30/how-to-use-the-ifna-function/

Use it to return nothing "" instead of the error.

=IFNA(LOOKUP(2, 1/((COUNTIF($G$2:G2, $C$3:$C$10)=0)*($E$3=$B$3:$B$10)), $C$3:$C$10), "")

Incredibly helpful , thank you so much!!

Joe,

you are welcome!

Hello, I have one question about the new/smaller formula. Pardon my lack of understanding, but what does the "2" and "1" at the beginning of the formula represent? This works for me, but I have no idea what these mean or what changing them would do. Thank You

Hi Nicolas

I am using 2 in order to make sure it is larger than any other numerical value in the array 1/((COUNTIF($G$2:G2, $C$3:$C$10)=0)*($E$3=$B$3:$B$10)).

The LOOKUP function is very different from most other functions because it ignores error values and you don't need to enter it as an array formula. Excel returns error value #DIV/0! if we divide 1 with 0 (zero).

Example, 1/{1; 0; 1} returns {1; #DIV/0!; 1}

LOOKUP(2, 1/((COUNTIF($G$2:G2, $C$3:$C$6)=0)*($E$3=$B$3:$B$6)), $C$3:$C$6)

becomes

LOOKUP(2, {1; #DIV/0!; 1}, $C$3:$C$6)

and returns the value in cell C6.

There is an explanation here as well: https://www.get-digital-help.com/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/

This is perfect except I only need the values that are not repeatedly in the column like the A, HOW DO i DO THAT?

Carlo,

This formula extracts unique values (not repeated) based a condition:

=LOOKUP(2, 1/((COUNTIF($G$2:G2, $C$3:$C$10)=0)*($E$3=$B$3:$B$10)*(COUNTIF($C$3:$C$10, $C$3:$C$10)=1)), $C$3:$C$10)

instead of e3 matching exactly with a value in row B, how do I create a unique distinct list of values from row C for any cells in B that contain the text found in E3?

For example I want to display the account IDs of any person who's name contains the letters "smith" which is entered in cell E3.

This article is most helpful. Is it possible to put more than one value in cell C7? I am trying to extract values that begin with two different strings. Thank-you.

Hello Oscar,

This is incrdible, but can you help me?

https://postimg.cc/XBdKzK6X

I add 1 item and the result #N/A

hi please solve the issue if you can.

Contact_number Employee_ID

945678452 2356

947856413 4562

978456897 3784

875647894 2356

987564123 4562

566212224 24578

Result i want in other sheet like

Employee_ID Contact_number

2356 945678452

2356 875647894

4562 947856413

4562 987564123

3784 978456897

24578 566212224

i mean contact number corresponding to Employee_id.i used vlookup but its giving me the only first instance of that Employee_id. second contact number its not showing.Please help.