SYNTAX:

CASE(Cell Range, Index, Sort) 

Cell Range:  Identifies the cell range the contains the list of values to pull from. 

Index: Which value in the list to return.  Ex, use 2 to get the 2nd value in the list.

Sort: Sets the sort order for the list of values. 

Sort	Description
"N"	No Sorting.
"D"	Descending (Highest to Lowest).  Works with text or numeric values
"A"	Ascending (Lowest to Highest).  Works with text or numeric values
"DD"	Date Descending (Highest to Lowest).
"DA"	Date Ascending (Lowest to Highest)

EXAMPLES:

1. Get the 3rd value from a list of values, no sorting. 

=VFL(C3:C33,3,"N") - Returns 3.3, the 3rd value. Note: Blank cells are ignored.

2.  Get the 2nd lowest value from the list. 

=VFL(C$3:C$33,2,"A") - Returns 2.62, sort the list Ascending (lowest to highest) and pick the 2nd value.

3. Get the 2nd lowest date from the list. 

=STR(VFL(B3:B32,2,"DA")) - Returns 4/2/2018, sort the dates ascending and return the 2nd value.  NOTE: STR must be used to return the value as text.  By default, VFL returns a number.  Use DFORMAT to format the date returned.

4. Get the 2nd value from a list of sorted text values (Ascending).

=STR(VFL(G3:G33,2,"A")) - Returns Ed, the 2nd one in the list when sorted alphabetically (Ascending).  Note blanks are ignored.

 

SEE ALSO: STR, DFORMAT