Home : VFL
Q14502 - INFO: VFL
VFL returns a Value From a List of values (cell range) 

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

Related Articles
No Related Articles Available.

Article Attachments
No Attachments Available.

Related External Links
No Related Links Available.
Help us improve this article...
What did you think of this article?

poor 
1
2
3
4
5
6
7
8
9
10

 excellent
Tell us why you rated the content this way. (optional)
 
Approved Comments...
No user comments available for this article.
Created on 5/15/2018 4:30 PM.
Last Modified on 5/16/2018 7:57 AM.
Last Modified by Scott Dorner.
Article has been viewed 389 times.
Rated 0 out of 10 based on 0 votes.
Print Article
Email Article