## Vlookup – Return multiple unique distinct values in excel

How to return multiple values using vlookup in excel and removing duplicates?

my sheet is setup as follows

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

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:

This answer contains no Vlookup function.

### Array formula in cell E8:

### How to create an array formula

- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar. See picture below.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.

### Named ranges

Category C3:C6

Item D3:D6

### Create named ranges

- Select cell range C3:C6
- Type
*Category*in name box. See picture above. - Repeat with remaining ranges

### Copy array formula

- Select cell E8
- Copy cell ( Ctrl + c)
- Select cell E9
- Paste (Ctrl - v)

### Remove errors (Excel 2007)

### Explaining array formula in cell E8

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

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

$C$8=Category

becomes

$C$8=$C$3:$C$6

becomes

"Coffee"={"Coffe"; "Coffe"; "Coffe"; "Coffe"}

and returns

{"TRUE"; "TRUE"; "TRUE"; "TRUE"}

**Step 2 - Remove previous values (duplicates) from list and return a boolean array**

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

(COUNTIF($E$7:E7, Item)=0)

becomes

(COUNTIF($E$7:E7, $D$3:$D$6)=0)

becomes

(COUNTIF("", {"Espresso";"Espresso";"Americano";"Americano"})=0)

becomes

({0;0;0;0}=0)

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

**Step 3 - Convert values in boolean array to corresponding row numbers**

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

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

becomes

IF({"TRUE"; "TRUE"; "TRUE"; "TRUE"}*{"TRUE"; "TRUE"; "TRUE"; "TRUE"}, ROW(Category)-MIN(ROW(Category))+1, "")

becomes

IF({"TRUE"; "TRUE"; "TRUE"; "TRUE"}, ROW(Category)-MIN(ROW(Category))+1, "")

becomes

IF({"TRUE"; "TRUE"; "TRUE"; "TRUE"}, {3;4;5;6}-MIN(ROW(Category))+1, "")

becomes

IF({"TRUE"; "TRUE"; "TRUE"; "TRUE"}, {3;4;5;6}-MIN({3;4;5;6})+1, "")

becomes

IF({"TRUE"; "TRUE"; "TRUE"; "TRUE"}, {3;4;5;6}-3+1, "")

becomes

IF({"TRUE"; "TRUE"; "TRUE"; "TRUE"}, {1;2;3;4}, "")

and returns {1;2;3;4}

**Step 4 - Return the k-th smallest number**

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

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

becomes

SMALL({1;2;3;4}, 1)

becomes

SMALL({1;2;3;4}, 1)

and returns 1.

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

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

becomes

=INDEX(Item, 1)

and returns Espresso in cell E8.

### Download excel file

Vlookup - Return multiple unique distinct values.xls

(Excel 97-2003 Workbook *.xls)

### Functions in this article:

**VLOOKUP(**lookup_value; table_array; col_index_num; [range_lookup]**)**

Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. By default, the table must be sorted in ascending order.

**IF(**logical_test;[value_if:true];[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**SMALL(**array,k**)** returns the k-th smallest number in this data set.

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

**MIN(**number1,[number2]**)**

Returns the smallest number in a set of values. Ignores logical values and text

**ROW(**reference**)** returns the rownumber of a reference

### Recommended blog articles

- Vlookup a range in excel
- Vlookup – Return multiple unique distinct values in excel
- Excel udf: Fuzzy lookups
- Fuzzy vlookup (excel array formula)
- Excel udf: Lookup and return multiple values concatenated into one cell
- Search case sensitive and return multiple values in excel
- Vlookup with multiple matches returns a different value in excel
- Lookup values in a range using two or more criteria and return multiple matches in excel, part 2
- Explaining a formula: Lookup values in a range using two or more criteria and return multiple matches in excel
- Lookup using two criteria in excel
- Vlookup of three columns to pull a single record
- Sum adjacent values from a range using multiple lookup values in excel
- Lookup values in a range using two or more criteria and return multiple matches in excel
- Sum adjacent values using multiple lookup text values in a column in excel
- Vlookup with 2 or more lookup criteria and return multiple matches in excel
- Lookup a value in a list and return multiple matches in excel
- How to return multiple values using vlookup 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)

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:

http://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