Difference between revisions of "Manuals/calci/VLOOKUP"
Jump to navigation
Jump to search
(Created page with "<div id="6SpaceContent" class="zcontent" align="left"> <font color="#484848"><font face="Arial, sans-serif"><font size="2">'''VLOOKUP'''</font></font></font><font color="#484...") |
|||
| Line 1: | Line 1: | ||
| − | + | =VLOOKUP(lkup_val, table_arr, column_index_num, range_lkup)= | |
| − | + | where, | |
| + | *'''lkup_val''' is the value to be matched from an array or first column of the table, | ||
| + | *'''table_arr''' refers to two or more columns of data values, | ||
| + | *'''column_index_num''' is the column number in the table from which the matching should be returned, and | ||
| + | *'''range_lkup''' is an optional logical value that specifies to find an exact match or approximate match of the mentioned lookup value. | ||
| − | + | VLOOKUP() looks up for a value in the first column of a table array and gives a value in the same row from another column in the table array. | |
| − | + | == Description == | |
| − | + | VLOOKUP(lkup_val, table_arr, column_index_num, range_lkup) | |
| − | + | *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 '''lkup_val''' is less than 'smallest value in first column of table', Calci displays #NULL error message. |
| + | *If '''column_index_num''' is 1, Calci returns the value in first column in table array. If '''column_index_num''' is 2, Calci returns the value in second column in table array and so on. | ||
| + | *For '''column_index_num''' < 1 or '''column_index_num''' > '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 '''range_lkup''' can be TRUE or FALSE. | ||
| + | *If '''range_lkup''' is TRUE or Omitted, Calci returns exact or approximate match of the value. | ||
| + | *Approximate value is the largest value that is <= '''lkup_val'''. | ||
| + | *If '''range_lkup''' 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 in '''Vector Form''' - | ||
| + | <div id="2SpaceContent" class="zcontent" align="left"> | ||
| − | + | {| id="TABLE3" class="SpreadSheet blue" | |
| + | |- class="even" | ||
| + | | class="sshl_f" |'''Number''' | ||
| + | | class=" " |'''Square''' | ||
| + | | class=" " |'''Cube''' | ||
| − | + | |- class="odd" | |
| − | - | + | | class="sshl_f" |1 |
| − | + | | class=" " |1 | |
| + | | class=" " |1 | ||
| − | + | |- class="even" | |
| + | | class="sshl_f" |2 | ||
| + | | class=" " |4 | ||
| + | | class=" " |8 | ||
| − | + | |- class="odd" | |
| − | + | | class="sshl_f" |3 | |
| − | + | | class=" " |9 | |
| + | | class=" " |27 | ||
| − | + | |- class="even" | |
| + | | class="sshl_f" |4 | ||
| + | | class=" " |16 | ||
| + | | class=" " |64 | ||
| − | + | |- class="odd" | |
| + | | class="sshl_f" |5 | ||
| + | | class=" " |25 | ||
| + | | class=" " |125 | ||
| − | + | |- class="even" | |
| + | | class="sshl_f" |6 | ||
| + | | class=" " |36 | ||
| + | | class=" " |216 | ||
| + | |} | ||
| − | + | =VLOOKUP(3,A2:C7,2) : Looks up for '3' in Number Column and returns value from Square column in the same row. <br />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. <br />Returns '''125''' as a result. | ||
| + | =VLOOKUP(5.5,A2:C7,2,TRUE) : Looks up for the approximate match for '5.5' in Number Column, matches the nearest value '5' and returns value from Square column in same row. <br />Returns '''25''' as a result. | ||
| + | |||
| + | == See Also == | ||
| − | [ | + | *[[Manuals/calci/LOOKUP | LOOKUP]] |
| + | *[[Manuals/calci/HLOOKUP | HLOOKUP]] | ||
| + | *[[Manuals/calci/MATCH | MATCH]] | ||
| − | + | == References == | |
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | + | *[http://en.wikipedia.org/wiki/Lookup_table#VLOOKUP VLOOKUP] | |
| − | + | *[http://en.wikipedia.org/wiki/Comparison_of_programming_languages_(string_functions)#Find Find String] | |
Revision as of 17:23, 15 February 2014
VLOOKUP(lkup_val, table_arr, column_index_num, range_lkup)
where,
- lkup_val is the value to be matched from an array or first column of the table,
- table_arr refers to two or more columns of data values,
- column_index_num is the column number in the table from which the matching should be returned, and
- range_lkup is an optional logical value that specifies to find an exact match or approximate match of the mentioned lookup value.
VLOOKUP() looks up for a value in the first column of a table array and gives a value in the same row from another column in the table array.
Description
VLOOKUP(lkup_val, table_arr, column_index_num, range_lkup)
- 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 lkup_val is less than 'smallest value in first column of table', Calci displays #NULL error message.
- If column_index_num is 1, Calci returns the value in first column in table array. If column_index_num is 2, Calci returns the value in second column in table array and so on.
- For column_index_num < 1 or column_index_num > '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 range_lkup can be TRUE or FALSE.
- If range_lkup is TRUE or Omitted, Calci returns exact or approximate match of the value.
- Approximate value is the largest value that is <= lkup_val.
- If range_lkup 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 in Vector Form -
| 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 the 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.