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 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))

Watch a video where I explain the formula

https://www.youtube.com/watch?v=er3oED4Hr5w

How to enter an array formula

Double click on cell G3
Copy and paste above formula to cell G3
Press and hold CTRL + SHIFT simultaneously
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}




Advanced formulas Concatenate Based on a condition Unique distinct values Drop-down list Join without TEXTJOIN Lookup & return values Compare Criteria, two lists Sums, return unique Extract missing values Common values 3 lists Two price lists Records in two tables Common records Update recent values(2) Missing values two cols Update recent values Values shared by 2 rngs Vals not shared 2 rngs Values shared by 3 rngs Extract not shared vals Shared values 2 cols Missing values 2 cols Combine merge Merge tables Merge rows - condition Combine ranges [UDF] Merge matching rows Merge 2 cols w. blanks Consolidate sheets Combine sheets Merge two columns Merge 3 columns Count values Pattern in cell value Overlapping time Containing any list value Unique distinct/condition Count colored cells Equal values per row Count overlapping days Sort count freq. condi. Count overlap. days(2) Count overlap. days(1) Contiguous values Count students Count filtered table Groups of each value Weekday in date range Weekday date range(2) Cells between value Entries date & time rng Unique distinct - criteria Unique dist. rec. criteria Digits - no duplicates Based on criteria Count groups in calendar Uniq. dist. based dates Count based on date Numbers across sheets Cells between values Most freq/date range Unique distinct months Uniq. dist. 2 cols Unique dist. values Dates Schedule -> date rngs Overlapping time Count overlapping days Date from week & day Count overlap. days(2) Count overlap. days(1) Missing dates Overlap. date ranges Overlapping ranges Find overlap. ranges Min/max date overlap. Date ranges overlap Consecutive dates Count overlap days Prevent overlapping Find date range based on a date Create date ranges Weekday in date range Calc dates in project Weeks from ranges Weekday date range(2) Entries date & time rng Overlap. date ranges Overlap rngs - condition Count groups in calendar Dates from overlap rngs Dates from date rngs Date rngs from dates Dates not in rngs Overlapping ranges Based on date criteria Date, week, month Dupes - date, wk, month Year - month from dates Match date in date rng Latest date - condition Most freq/date range Find min/max date rng Blank between quarters Dupe vals sorted date Create date range Lookup the nearest date Based on a date range Machine utilization Date based on wkday Find numbers in sum Numbers closest to sum No net close to zero Find numbers in sum Numbers in sum Overlapping Overlapping time Count overlapping days Count overlap. days(2) Count overlap. days(1) Missing dates Overlap. date ranges Overlapping ranges Find overlap. ranges Min/max date overlap. Date ranges overlap Count overlap days Prevent overlapping Overlap. date ranges Overlap rngs - condition Overlapping ranges Unique values Case sensitive Unique strings [UDF] Sort digits in rng Based on date criteria From range Sort unique A-Z Min unique number Sort based on date Sum if unique Two columns One column Unique distinct values One column Two columns Three columns Remove blanks Uniq. dist. - condition Adjacent contains string Adjacent has text Contains string Based on criteria Sorted frequency Sorted by sum Sorted alphabetically Sorted A->Z Sorted & no blanks - rng Sort length From a cell range Sorted A-Z from range Contains string in rng Begins with criterion Based on a date range Based on dates Year - month from dates Based on date criteria Extract filtered table Extract & sum condition Two conditions Sum uniq. dist. rec. Assign randomly unique Sums, return unique Sorted, no blanks - rng Duplicate values Dupes A-Z condition Dupes w. exceptions Filter duplicate values using critera Common values 3 lists Label duplicate records Dupes - date, wk, month Dupes from rng contain Filter dupes begins w. Min unique number Dupe vals sorted date Sort dupes A-Z Dupes from range Dupes from 3 cols Dupes from 1 col Dupes from 2 cols Create a list of duplicates where adjacent cell value meets a condition Sort values Sort a column A-Z Sort from 2 cols A-Z Sort rng A-Z Sort column - frequency Frequency - criteria Sort rng - frequency Sort count freq. condi. Sort two columns Sort records Sort table Sort adj. txt horiz. (1) Sort monthly Sort table custom Lookup/Sort custom Sort random order Sort based on length Reverse list Sort dupes A-Z Sort unique A-Z Sort unique A-Z blanks Sort cell delimiter [VBA] Sort Excel table [VBA] Sort based on proximity Sort dates - criteria Vlookup function VLOOKUP function Discount percentages Multiple conditions Lookup values in rng Lookup value in rng Three columns Two datasets Vlookup and return multiple values 5 easy ways Multiple conditions Sorted from A to Z Sorted A-Z - condition Multiple conditions Vlookup range Vlookup rng - records Vlook col - return rng 1 result per search Horizontal vlookup Vlookup - Drop-down Multiple sheets Search and return multiple values Nested Search Multiple conditions Interactive formula Search for string Search records Wildcard vlookup Multiple strings Criteria - AND logic Strings in any 2 cols Strings in 2 cols each Strings in column Contains string - column Filter records Extract using criteria Extract using criteria(2) Extract using criteria(3) Recs betw. this & that Records betw. 2 dates Date range & condition Search text string Filter neg. values Containing digits Records Sort based on freq. Not shared records Sort table custom Records [AND logic] Records in two tables Unique records Common records Common records Duplicate columns Unique distinct records Extract unique distinct records from two data sets Sort based on freq. Filter unique distinct records using criteria Count uniq. dist. [2010] Unique dist. rec. criteria Records - condition(2) Records - condition(1) Filter unique distinct records Count unique distinct records Count unique distinct records Duplicate records Extract duplicate records Count records Weekly Blog EMAIL Get a FREE PDF book! Email Formula categories Count Dates Extract Index and Match Logic Lookups String manipulation Sum Time Vlookup Welcome! I am Oscar and here to help you out. Feel free to comment and ask Excel questions. Make sure you subscribe to my newsletter so you don't miss new blog articles.	Use VLOOKUP and return multiple values sorted from A to Z Excel > VLOOKUP and return multiple values > Use VLOOKUP and return multiple values sorted from A to Z Share on Facebook. Share on Twitter. Share on LinkedIn Pin It! Author: Oscar Cronquist Article last updated on February 14, 2018 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)) Watch a video where I explain the formula https://www.youtube.com/watch?v=er3oED4Hr5w Recommended article Unique distinct list sorted alphabetically based on a condition The array formula in cell E6 filters values in column C based on value in cell E3, the output is […] Unique distinct list sorted alphabetically based on a condition How to enter an array formula Double click on cell G3 Copy and paste above formula to cell G3 Press and hold CTRL + SHIFT simultaneously Press Enter once 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}

get-digital-help.com