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
Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.Don´t forget to add my
May 13th, 2011 at 12:51 pm
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.
May 13th, 2011 at 10:52 pm
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
May 16th, 2011 at 8:23 am
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?
May 16th, 2011 at 10:57 am
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"
May 17th, 2011 at 9:49 am
Ahmed Ali,
I am not sure I understand.
Send an example file without sensitive information using the contact form on this page.
May 18th, 2011 at 1:59 am
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.
May 18th, 2011 at 7:32 am
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:
May 18th, 2011 at 12:31 pm
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.
May 19th, 2011 at 9:14 am
Sean,
read this post: Filter unique distinct records with a condition in excel 2007
August 21st, 2011 at 9:57 pm
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
August 22nd, 2011 at 1:29 pm
Sugato,
Thanks!
December 29th, 2011 at 3:51 pm
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
January 3rd, 2012 at 5:28 pm
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.
January 6th, 2012 at 1:45 pm
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)
February 10th, 2012 at 5:55 pm
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.
March 12th, 2012 at 3:19 pm
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