Difference between revisions of "Manuals/calci/VLOOKUP"
Jump to navigation
Jump to search
| Line 1: | Line 1: | ||
=VLOOKUP(lkup_val, table_arr, column_index_num, range_lkup)= | =VLOOKUP(lkup_val, table_arr, column_index_num, range_lkup)= | ||
| + | <div style="font-size:30px">'''VLOOKUP (Value,Array,Column,ApproximateMatchFlag) '''</div><br/> | ||
where, | where, | ||
| − | *''' | + | *'''Value''' is the value to be matched from an array or first column of the table, |
| − | *''' | + | *'''Array''' refers to two or more columns of data values, |
| − | *''' | + | *'''Column''' is the column number in the table from which the matching value should be returned, and |
| − | *''' | + | *'''ApproximateMatchFlag''' is an optional logical value that specifies whether to find an exact match or approximate match of the mentioned lookup value. |
| − | VLOOKUP() looks | + | VLOOKUP(), looks in the first column of an array and moves across the row to return the value of a cell. |
== Description == | == Description == | ||
| − | VLOOKUP( | + | VLOOKUP (Value,Array,Column,ApproximateMatchFlag) |
*The 'V' in VLOOKUP stands for vertical. This function looks the value column wise. | *The 'V' in VLOOKUP stands for vertical. This function looks the value column wise. | ||
| Line 17: | Line 18: | ||
*VLOOKUP function is not case-sensitive. | *VLOOKUP function is not case-sensitive. | ||
| − | *If ''' | + | *If '''Value''' is less than 'smallest value in first column of table', Calci displays #NULL error message. |
| − | *If ''' | + | *If '''Column''' is 1, Calci returns the value from the first column in table array. If '''column_index_num''' is 2, Calci returns the value from the second column in table array and so on. |
| − | *For ''' | + | *For '''Column''' < 1 or '''Column''' > 'number of columns in array', Calci returns #NULL error message. |
*The values in first column should be arranged in ascending order for correct results. | *The values in first column should be arranged in ascending order for correct results. | ||
| − | *Argument ''' | + | *Argument '''ApproximateMatchFlag''' can be TRUE or FALSE. |
| − | *If ''' | + | *If '''ApproximateMatchFlag''' is TRUE or Omitted, Calci returns exact or approximate match of the value. |
*Approximate value is the largest value that is <= '''lkup_val'''. | *Approximate value is the largest value that is <= '''lkup_val'''. | ||
| − | *If ''' | + | *If '''ApproximateMatchFlag''' is FALSE, Calci searches only for the exact match. If exact match is not found, Calci returns #NULL error message. |
*For invalid arguments, Calci displays #NULL error message. | *For invalid arguments, Calci displays #NULL error message. | ||
Revision as of 15:49, 9 August 2018
VLOOKUP(lkup_val, table_arr, column_index_num, range_lkup)
VLOOKUP (Value,Array,Column,ApproximateMatchFlag)
where,
- Value is the value to be matched from an array or first column of the table,
- Array refers to two or more columns of data values,
- Column is the column number in the table from which the matching value should be returned, and
- ApproximateMatchFlag is an optional logical value that specifies whether to find an exact match or approximate match of the mentioned lookup value.
VLOOKUP(), looks in the first column of an array and moves across the row to return the value of a cell.
Description
VLOOKUP (Value,Array,Column,ApproximateMatchFlag)
- The 'V' in VLOOKUP stands for vertical. This function looks the value column wise.
- Arguments containing text values entered as a part of command, should be enclosed in quotes (e.g. "JOHN").
- VLOOKUP function is not case-sensitive.
- If Value is less than 'smallest value in first column of table', Calci displays #NULL error message.
- If Column is 1, Calci returns the value from the first column in table array. If column_index_num is 2, Calci returns the value from the second column in table array and so on.
- For Column < 1 or Column > 'number of columns in array', Calci returns #NULL error message.
- The values in first column should be arranged in ascending order for correct results.
- Argument ApproximateMatchFlag can be TRUE or FALSE.
- If ApproximateMatchFlag is TRUE or Omitted, Calci returns exact or approximate match of the value.
- Approximate value is the largest value that is <= lkup_val.
- If ApproximateMatchFlag is FALSE, Calci searches only for the exact match. If exact match is not found, Calci returns #NULL error message.
- For invalid arguments, Calci displays #NULL error message.
Examples
Following table shows the use of VLOOKUP function -
| Number | Square | Cube |
| 1 | 1 | 1 |
| 2 | 4 | 8 |
| 3 | 9 | 27 |
| 4 | 16 | 64 |
| 5 | 25 | 125 |
| 6 | 36 | 216 |
=VLOOKUP(3,A2:C7,2) : Looks up for '3' in Number Column and returns value from Square column
in the same row. Returns 9 as a result. =VLOOKUP(5,A2:C7,3,FALSE) : Looks up for exact match for '5' in Number Column and returns value from
Cube column in the same row. Returns 125 as a result. =VLOOKUP(5.5,A2:C7,2,TRUE) : Looks up for approximate match for '5.5' in Number Column,
matches the nearest value '5' and returns value from Square column in same row.
Returns 25 as a result.
Related Videos
See Also
References