## How to create a unique distinct list where other columns meet two criteria

**Question:** How do I create a unique distinct list where other columns meet two criteria using excel array formula?

**Answer:**

### Excel 2007 array formula in H2:

Recommended reading

**Create unique distinct list from column where an adjacent column meets criteria**

Comments(2) Filed in category: Excel, Unique distinct values

### How to create an array formula

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

**Learn the basics of Excel arrays**

Comments(2) Filed in category: Count values, Excel

Copy cell H2. Paste down to cell H16.

### Earlier Excel versions, array formula in H2:

Copy cell H2. Paste down to cell H16.

Recommended reading:

**Unique distinct list to be created from a column where an adjacent column has text cell values**

Comments(5) Filed in category: Excel, Unique distinct values

### Named ranges

List (C2:C16)

Category1 (A2:A16)

Category2 (B2:B16)

Criteria1 (F1)

Criteria2 (F2)

Recommended reading:

**Vlookup – Return multiple unique distinct values**

Comments(45) Filed in category: Excel, Unique distinct values, VLOOKUP and return multiple values

### How to create named ranges

- Select cell range C2:C16
- Type
*List*in name box

### How to implement array formula to your workbook

Change named ranges. If your unique distinct list starts at, for example, F3. Change H1:$H$1 in the above formula to F2:$F$2. The ranges don´t have to be adjacent.

### Explaining excel 2007 array formula in cell H2

**Step 1 - Create boolean array with values indicating unique distinct values**

=IFERROR(INDEX(List, MATCH(0, **COUNTIF(H1:$H$1, List)**+IF(Category2<>Criteria2, 1, 0)+IF(Category1<>Criteria1, 1, 0), 0)), "")

COUNTIF(H1:$H$1, List)

becomes

COUNTIF("Distinct unique list", {4; 2; 1; 2; 2; 4; 2; 3; 1; 1; 3; 4; 2; 5; 7})

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

Comments(5) Filed in category: Excel

**Step 2 - Create boolean array with values indicating first criterion**

=IFERROR(INDEX(List, MATCH(0, COUNTIF(H1:$H$1, List)+IF(Category2<>Criteria2, 1, 0)+**IF(Category1<>Criteria1, 1, 0)**, 0)), "")

IF(Category1<>Criteria1, 1, 0)

becomes

IF({"BB"; "AA"; "AA"; "BB"; "BB"; "BB"; "BB"; "BB"; "AA"; "BB"; "BB"; "BB"; "AA"; "BB"; "BB"}<>"BB", 1, 0)

and returns {0;1;1;0;0;0;0;0;1;0;0;0;1;0;0}.

Comments(9) Filed in category: Excel

**Step 3 - Create boolean array with values indicating second criterion**

=IFERROR(INDEX(List, MATCH(0, COUNTIF(H1:$H$1, List)+**IF(Category2<>Criteria2, 1, 0)**+IF(Category1<>Criteria1, 1, 0), 0)), "")

IF(Category2<>Criteria2, 1, 0)

becomes

IF({"A"; "B"; "A"; "B"; "A"; "B"; "A"; "B"; "A"; "B"; "A"; "B"; "A"; "B"; "C"}<>"A", 1, 0)

and returns {0; 1; 0; 1; 0; 1; 0; 1; 0; 1; 0; 1; 0; 1; 1}.

**Step 4 - Add boolean arrays**

=IFERROR(INDEX(List, MATCH(0, COUNTIF(H1:$H$1, List)+IF(Category2<>Criteria2, 1, 0)+IF(Category1<>Criteria1, 1, 0), 0)), "")

becomes

=IFERROR(INDEX(List, MATCH(0,{0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}+{0; 1; 0; 1; 0; 1; 0; 1; 0; 1; 0; 1; 0; 1; 1}+{0;1;1;0;0;0;0;0;1;0;0;0;1;0;0}, 0)), "")

becomes

=IFERROR(INDEX(List, MATCH(0,{0;2;1;1;0;1;0;1;1;1;0;1;1;1;1}, 0)), "")

**Step 4 - Return relative position of the first zero (0) in array**

=IFERROR(INDEX(List, MATCH(0,{0;2;1;1;0;1;0;1;1;1;0;1;1;1;1}, 0)), "")

becomes

=IFERROR(INDEX(List, 1), "")

Comments(12) Filed in category: Excel

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

=IFERROR(INDEX(List, 1), "")

becomes

=IFERROR(4, "")

Comments(14) Filed in category: Cross reference table, Excel, INDEX function

**Step 6 - Check if expression returns an error**

=IFERROR(4, "")

returns number 4 in cell H2.

Comments(0) Filed in category: Excel

### Download excel file.

create-a-list-of-distinct-values-from-two-criteria.xlsx

(Excel 2007 Workbook *.xlsx)

create-a-list-of-distinct-values-from-two-criteria.xls

(Excel 97-2003 Workbook *.xls)

### Category: Unique distinct values

5 easy ways to extract unique distinct values

Update: 30 Aug, 2017h You have quite a few options to choose from if you are looking for a way […]Comments(149) Filed in category: Advanced filter, Excel, Unique distinct values, Unique values

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 an unique distinct […]Comments(79) Filed in category: Excel, Unique distinct values

Create a unique distinct alphabetically sorted list extracted from a column

The formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. Unique […]Comments(53) Filed in category: Excel, Unique distinct values

Vlookup – Return multiple unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]Comments(45) Filed in category: Excel, Unique distinct values, VLOOKUP and return multiple values

Extract a unique distinct list by matching items that meet a criterion in excel

Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]Comments(40) Filed in category: Excel, Unique distinct values

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 […]Comments(31) Filed in category: Excel, Unique distinct values

Filter 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 […]Comments(24) Filed in category: Excel, Unique distinct values

Unique distinct values from multiple columns using array formula

Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]Comments(23) Filed in category: Excel, Unique distinct values

Extract unique distinct values from a filtered table (udf and array formula)

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]Comments(17) Filed in category: Excel, Sort values, Unique distinct values

### 19 Responses to “How to create a unique distinct list where other columns meet two criteria”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

your array formula examples are fantastic and incredibly useful, thank you! this one provides almost all the insight I need, except for sorting the result list. I've tried to apply the sorting techniques from some of your other examples and am just not getting it. Can you help?

well after a few hours I think I've figured it out, just need to handle blank rows.

{=IFERROR(INDEX(tbl2,SMALL(IF(SMALL(IF(((($F$2=Color)*($G$2=Texture)*(COUNTIF($I$1:I1,tbl2)=0))),COUNTIF(tbl2,"<"&tbl2)+1,""),1)=COUNTIF(tbl2,"<"&tbl2)+1,ROW(tbl2)-MIN(ROW(tbl2))+1),1)),"")}

where

tbl2 = the original list

'Color' is a range defined on a column next to tbl2, specifying a color for each entry. The same for 'Texture.'

F2 and G2 define the color and texture to be selected, and "I" defines the output column.

Congratulations!

You did it!

Thank you for your contribution!

I modified your named ranges for this post.

Array formula in H2:

=IFERROR(INDEX(List, SMALL(IF(SMALL(IF(((($F$2=Category2)*($F$1=Category1)*(COUNTIF($H$1:H1, List)=0))), COUNTIF(List, "<"&List)+1, ""), 1)=COUNTIF(List, "<"&List)+1, ROW(List)-MIN(ROW(List))+1), 1)), "") + CTRL + SHIFT + ENTER copied down as far as needed.

Oscar this site's been a great help. Having a problem with the above formula for earlier versions though. Should there be semicolons in it?

Andy Green,

Thanks!

No, I have removed the semicolons.

Thanks Oscar. Still getting "Too many arguments for this function.

Thinking about it, it may help if I tell you exactly what I'm trying to do. I'm trying to extract a list of unique values, based on a number of criteria, including some "OR's". Any ideas. I can normally find everything on here but this has me stumped

Andy Green,

It works now, download attached xls file or copy array formula.

Thanks for letting me know!

Oscar you're a star. You're gonna love me for this....How would I add an "OR" in there. ie Say there was a row such as....

BB C 7

As there's already a Category2"B" I can't add a "+IF(Category2"C", 1, 0)"

So how would I include that one? Any ideas or am I just pushing my luck?

Andy Green,

Named rangesList (C2:C16)

Category1 (A2:A16)

Category2 (B2:B16)

Criteria1 (F1)

Criteria2 (

F2:F3)Excel 2007 array formula in H2:Earlier Excel versions, array formula in H2:You are the Excel Joda!

Yoda quote:

"Feel the (excel) force!"

[...] tables to the web >> Excel Jeanie HTML 4 You can find the above formulas here..... How to create a unique distinct list where other columns meet two criteria | Get Digital Help - Micr... Count unique distinct records with a date and column criteria in excel 2007 | Get Digital Help - [...]

I've solved a similar problem inverting the results (1 and 0) of the "IF" clauses. I post it here in case someone would find it useful:

=INDEX(List; MATCH(0; COUNTIF($H$1:H1; List) + IF(Category>0;0;1) + IF(Category0 and <2)

Hi,

This is absolutely great and it's what I need. I do have one question and maybe it might be the operator (me). I download the xlsx version of the file and when I click into the formula to change the range for my data and click out of the cell it returns a blank. If I take out the IFERROR statement it shows a #N/A. I don't understand what I'm doing wrong. Can you help?

Thanks,

Sorry me again. It's the operator with the issue. I didn't change it back to an array formula.

Hi,

Is there a way to make this work on ranges? like, if a cell is larger than 3 and smaller than 5.

I'm sorry, I figured it out now.

I just replaced it with a ">" and "<" so it turns out FALSE and it worked.

Thanks for the code :)

Is there a way to do this without using the IFERROR? The speed of my workbook has ground to a halt after doing this formula. I have 1800 rows of data. Thanks in advance!