Home : VLOOKUP Spread Function
Q14138 - INFO: VLOOKUP Spread Function
VLOOKUP is used to search the first column of a range of cells, and then return a value from any cell on the same row of the range.

SYNTAX:

VLOOKUP(lookup_value,table cell range, col_index, match_type)

lookup_value - The value to search in the first column of the table cell range. The lookup_value argument can be a value or a reference. If the value you supply for the lookup_value argument is smaller than the smallest value in the first column of the table cell range, VLOOKUP returns no result.

table cell range: The range of cells (for example, A2:D8) that contains the data. The values in the first column of table_array are the values searched by lookup_value. These values can be text or numbers. Uppercase and lowercase text are equivalent.

col_index: The column number in the table cell range from which the matching value must be returned. A col_index_num argument of 1 returns the value in the first column in table cell range; a col_index_num of 2 returns the value in the second column in table_array, and so on.

match_type:  Sets whether you want an exact match (0) or approximate match (1).  If set to 1, an an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.   NOTE: If match_type is set to 1, the values in the first column of the table MUST BE in ascending sort order.  If set to 0 (zero), only exact matches will be returned and values in the first column do not need to be sorted.

NOTES:

See Also: Table Function

EXAMPLES:

=VLOOKUP(.6,A$2:D$10,2,1) returns 3.25:   Look for a value <= .6 (<= since match type is 1) in A2 thru A10 (first column of table cell range) and return the value from that row in column B (since col_index is 2)  

=STR(VLOOKUP(.835,A$2:D$10,4,0)) returns W.  When returning text, you must use the STR function. 

 

 

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 7/12/2013 12:59 PM.
Last Modified on 7/12/2013 4:02 PM.
Last Modified by Scott Dorner.
Article has been viewed 2813 times.
Rated 7 out of 10 based on 3 votes.
Print Article
Email Article