Difference between revisions of "Manuals/calci/VLOOKUP"
Jump to navigation
Jump to search
| (6 intermediate revisions by 3 users not shown) | |||
| Line 1: | Line 1: | ||
| − | =VLOOKUP( | + | <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 in the first column of an array and moves across the row to return the value of a cell. | |
| − | VLOOKUP() looks | ||
== 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 16: | ||
*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. | ||
== Examples == | == Examples == | ||
| − | Following table shows the use of VLOOKUP function | + | Following table shows the use of VLOOKUP function - |
<div id="2SpaceContent" class="zcontent" align="left"> | <div id="2SpaceContent" class="zcontent" align="left"> | ||
| Line 68: | Line 67: | ||
|} | |} | ||
| − | =VLOOKUP(3,A2:C7,2) : Looks up for '3' in Number Column and returns value from Square column in the same row. | + | =VLOOKUP(3,A2:C7,2) : Looks up for '3' in Number Column and returns value from Square column <br />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. | + | =VLOOKUP(5,A2:C7,3,FALSE) : Looks up for exact match for '5' in Number Column and returns value from <br />Cube column in the same row. Returns '''125''' as a result. |
| − | =VLOOKUP(5.5,A2:C7,2,TRUE) : Looks up for | + | =VLOOKUP(5.5,A2:C7,2,TRUE) : Looks up for approximate match for '5.5' in Number Column, <br />matches the nearest value '5' and returns value from Square column in same row. <br />Returns '''25''' as a result. |
| − | + | ||
| + | ==Related Videos== | ||
| + | |||
| + | {{#ev:youtube|GZX2iXfqALs|280|center|VLOOKUP}} | ||
| + | |||
== See Also == | == See Also == | ||
| Line 82: | Line 85: | ||
*[http://en.wikipedia.org/wiki/Lookup_table#VLOOKUP VLOOKUP] | *[http://en.wikipedia.org/wiki/Lookup_table#VLOOKUP VLOOKUP] | ||
*[http://en.wikipedia.org/wiki/Comparison_of_programming_languages_(string_functions)#Find Find String] | *[http://en.wikipedia.org/wiki/Comparison_of_programming_languages_(string_functions)#Find Find String] | ||
| + | |||
| + | |||
| + | |||
| + | *[[Z_API_Functions | List of Main Z Functions]] | ||
| + | |||
| + | *[[ Z3 | Z3 home ]] | ||
Latest revision as of 16:30, 9 August 2018
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