Unique distinct list sorted alphabetically based on a condition

=INDEX($C$3:$C$11, MATCH(SMALL(IF((COUNTIF($G$2:G2, $C$3:$C$11)=0)*($B$3:$B$11=$E$3), COUNTIF($C$3:$C$11, "<"&$C$3:$C$11), ""), 1), COUNTIF($C$3:$C$11, "<"&$C$3:$C$11), 0))

How to create an array formula

Double click on cell E6
Copy (Ctrl + c) and paste (Ctrl + v) array formula to cell
Press and hold Ctrl + Shift simultaneously
Press Enter once.
Release all keys.

There are now a beginning and ending curly bracket in the formula bar, like this: {=formula}
Don't enter these characters yourself.

Explaining array formula in cell E6

Read my explanation here: Create a unique distinct alphabetically sorted list, extracted from a column

Download excel *.xlsx file

Create a unique distinct alphabetically sorted list with criteria.xlsx

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Vlookup with 2 or more lookup criteria and return multiple matches

VLOOKUP and return multiple matches based on many criteria.

Vlookup across multiple sheets

This article demonstrates an array formula that searches two tables on two different sheets and returns multiple results. Sheet1 contains […]

VLOOKUP and return multiple values across columns

This article demonstrates a formula that lets you extract non-empty values across columns based on a condition. The image above […]

Use a drop down list to search and return multiple values

I will in this article demonstrate how to use a value from a drop-down list and use it to do […]

Search values distributed horizontally and return corresponding value

Question: Hi, The formula here works great but I can't figure out how to change it to work with data […]

Vlookup with multiple matches returns a different value

Linda asks in this post: How to return multiple values using vlookup in excel I tried using the formula above […]

Vlookup a cell range and return multiple values

VLOOKUP a multi-column range and return multiple values.

Lookup multiple values in different columns and return multiple values

Jason C asks: I have a set of data, like the one you used in the original example that also […]

Use VLOOKUP and return multiple values sorted from A to Z

The array formula in column G filters values in column C using a condition in cell E3, comparing it with […]

Comments (1)

One Response to “Unique distinct list sorted alphabetically based on a condition”

#Excel Super Links #128 – shared by David Hager | Excel For You says:

August 16, 2017 at 1:47 pm

[…] https://www.get-digital-help.com/2017/08/15/unique-distinct-list-sorted-alphabetically-and-based-on-… […]

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.

Contact Oscar

You can contact me through this contact form