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
Related posts:
Vlookup visible data in a table and return multiple values in excel
Vlookup with 2 or more lookup criteria and return multiple matches in excel
How to return multiple values using vlookup in excel
Vlookup with multiple matches returns a different value in excel
Array formula to look up a value and return multiple values in excel
Recommended categories
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:
The above array formula grows quickly if we add many critera, this array formula is smaller:
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.