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

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

Recommended articles

5 easy ways to extract Unique Distinct Values

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

5 easy ways to extract Unique Distinct Values

5 easy ways to VLOOKUP and return multiple values

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

5 easy ways to VLOOKUP and return multiple values

Use VLOOKUP and return multiple values sorted from A to Z

The array formula in column G filters values in column C using a condition in cell E3, comparing it with […]

Use VLOOKUP and return multiple values sorted from A to Z

#### How to enter an array formula

- Double click 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

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

#### Copy array formula

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

#### Explaining array formula in cell E8

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

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

becomes

{1;2;1;1;1;1;1;2}<>1

and returnsĀ {FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}

**Step 2 - Check earlier values above current cell**

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

becomes

COUNTIF("Unique distinct list",{"A";"A";"A";"A";"B";"B";"D";"C"})

and returns {0;0;0;0;0;0;0;0}

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

How to use the COUNTIF function

**Step 3 - Add arrays**

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}

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

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.

Identify the position of a value in an array.

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

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

becomes

INDEX($C$3:$C$10,1)

becomes

INDEX({"A";"A";"A";"A";"B";"B";"D";"C"},1)

and returns "A" in cell G3.

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

#### Download excel file

Vlookup - Return multiple unique distinct valuesv2.xlsx

(Excel 97-2003 Workbook *.xls)

5 easy ways to extract Unique Distinct Values

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

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

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 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 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 unique distinct values A to Z from a range and ignore blanks

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

### 57 Responses to “Vlookup – Return multiple unique distinct values”

### Leave a Reply to Oscar

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

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)

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 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/2009/10/25/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.

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,

Download this file:

ChocolateGal.xlsx

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

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/2009/03/30/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.