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
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
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 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)
becomes
COUNTIF("Unique distinct list",{"A";"A";"A";"A";"B";"B";"D";"C"})
and returns
{0;0;0;0;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))
becomes
INDEX($C$3:$C$10,1)
becomes
INDEX({"A";"A";"A";"A";"B";"B";"D";"C"},1)
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
becomes
1={1;2;1;1;1;1;1;2}
and returns
{TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; 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)
becomes
FILTER({"A"; "A"; "A"; "A"; "B"; "B"; "C"; "D"},{TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE})
and 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))
becomes
UNIQUE({"A";"A";"A";"B";"B";"C"})
and returns
{"A";"B";"C"}.
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 […]
The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]
Excel categories
59 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.
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:
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.
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
[…] 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/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.
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.