The array formula in column G filters values in column C using a condition in cell E3, comparing it with values in adjacent column B. The filtered values are then sorted from A to Z.

It is possible to build a formula around the VLOOKUP function but it would be big, the following formula is smaller and easier to understand.

Array formula in cell G3:

=INDEX($C$3:$C$10, MATCH(SMALL(IF($E$3=$B$3:$B$10, COUNTIF($C$3:$C$10, "<"&$C$3:$C$10), ""),ROWS($A$1:A1)), COUNTIF($C$3:$C$10,"<"&$C$3:$C$10), 0))

Recommended article

How to sort cells filtered by two dates

Question: I have a list containg dates and values. How do I sort values between two specific dates? Answer: Yellow […]

Comments(12) Filed in category: Dates, Excel, Sort values

How to enter an array formula

  1. Double click on cell G3
  2. Copy and paste above formula to cell G3
  3. Press and hold CTRL + SHIFT simultaneously
  4. Press Enter once
    1. Release all keys

Examine the formula bar and you will see that the formula now has a beginning and ending curly bracket. Don't enter these characters yourself, they appear automatically. Example, {=array_formula}

Recommended article

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

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

Explaining formula in cell G3

Step 1 - Sort values in column C

COUNTIF($C$3:$C$10, "<"&$C$3:$C$10)

becomes

COUNTIF({"F"; "S"; "G"; "E"; "B"; "N"; "W"; "A"},{"<F"; "<S"; "<G"; "<E"; "<B"; "<N"; "<W"; "<A"})

and returns

{3;6;4;2;1;5;7;0}

Recommended article

COUNTIF function

Counts the number of times a value exists in a cell range.

Comments(5) Filed in category: Excel, Functions

Step 2 - Extract sort rank numbers for chosen category

IF($E$3=$B$3:$B$10, COUNTIF($C$3:$C$10, "<"&$C$3:$C$10), "")

becomes

IF($E$3=$B$3:$B$10, {3;6;4;2;1;5;7;0}, "")

becomes

IF($E$3=$B$3:$B$10, {3;6;4;2;1;5;7;0}, "")

becomes

IF(1={1;2;1;2;1;1;2;2},{3;6;4;2;1;5;7;0},"")

becomes

IF({TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE},{3;6;4;2;1;5;7;0},"")

and returns

{3;"";4;"";1;5;"";""}

Recommended article

IF function explained

Check if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

Comments(9) Filed in category: Excel, Functions

Step 3 - Find k-th smallest value in array

SMALL(IF($E$3=$B$3:$B$10,COUNTIF($C$3:$C$10,"<"&$C$3:$C$10),""),ROWS($A$1:A1))

becomes

SMALL({3;"";4;"";1;5;"";""},ROWS($A$1:A1))

becomes

SMALL({3;"";4;"";1;5;"";""},1)

and returns 1.

Recommended article

SMALL function and LARGE function

This function lets you extract any number in a cell range based on sort rank.

Comments(12) Filed in category: Excel, Functions

Step 4 - Match sort rank to find relative position

MATCH(SMALL(IF($E$3=$B$3:$B$10, COUNTIF($C$3:$C$10, "<"&$C$3:$C$10), ""),ROWS($A$1:A1)), COUNTIF($C$3:$C$10,"<"&$C$3:$C$10), 0)

becomes

MATCH(1, {3;6;4;2;1;5;7;0}, 0)

and returns 5.

Recommended article

MATCH function

Identify the position of a value in an array.

Comments(12) Filed in category: Excel, Functions

Step 5 - Return values

INDEX($C$3:$C$10,MATCH(SMALL(IF($E$3=$B$3:$B$10,COUNTIF($C$3:$C$10,"<"&$C$3:$C$10),""),ROWS($A$1:A1)),COUNTIF($C$3:$C$10,"<"&$C$3:$C$10),0))

becomes

INDEX($C$3:$C$10,5)

becomes

INDEX({"F";"S";"G";"E";"B";"N";"W";"A"},5)

and returns B in cell G3.

Recommended article

INDEX function explained

Fetch a value in a data set based on coordinates.

Comments(14) Filed in category: Excel, Functions, Index

Tip! You can easily filter values if you convert your data to an excel table and then sort them:

Excel tables

An Excel table allows you to easily sort, filter and sum values in a data set where values are related.

Comments(0) Filed in category: Built-in features, Excel, Excel table

Download excel *.xlsx file

Use VLOOKUP and return multiple values sorted from A to Z.xlsx

Recommended articles

Sorting text cells using array formula in excel

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

Comments(81) Filed in category: Excel, Sort values

How to return multiple values using vlookup in excel

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

Comments(441) Filed in category: Excel, VLOOKUP and return multiple values

Unique distinct records sorted by frequency

Sara asks: How can you use large with multiple criteria?? Example looking for top 5 of a list based on […]

Comments(3) Filed in category: Excel, Sorted unique distinct values

Create a unique distinct list and sort by occurrances from large to small

Question: How do I create a new unique distinct list from a column. I also want the list sorted from large […]

Comments(15) Filed in category: Excel, Sorted unique distinct values

Create a drop down list containing alphabetically sorted values in excel

Overview This article describes how to create a drop down list populated with sorted values from A to Z. The […]

Comments(15) Filed in category: Drop down lists, Excel

Create a unique distinct sorted list containing both numbers text removing blanks

Table of contents Create a unique distinct sorted list containing both numbers text removing blanks Create a unique distinct sorted […]

Comments(24) Filed in category: Excel, Sorted unique distinct values