Difference between revisions of "Manuals/calci/LOOKUP"
Jump to navigation
Jump to search
(Created page with "<div id="16SpaceContent" align="left"><div class="ZEditBox" align="justify"> Syntax </div></div> ---- <div id="2SpaceContent" align="left"><div class="ZEditBox" align=...") |
|||
| Line 1: | Line 1: | ||
| − | + | =LOOKUP(lkup_val, lkup_vector, rslt_vector)= | |
| − | + | *where, '''lkup_val''' is the value to be matched from an array or range of values, | |
| + | *'''lkup_vector''' is the array of values or reference to one row or one column containing values, and | ||
| + | *'''rslt_vector''' is a range that contains only one-row or one-column and is same size as lkup_vector. | ||
| − | + | LOOKUP() looks up for a specified value in one-row range or one-column range or an array, and displays a value from same position in second one-row range or one-column range or an array. | |
| − | ---- | ||
| − | |||
| − | + | == Description == | |
| − | + | LOOKUP(lkup_val, lkup_vector, rslt_vector) | |
| − | |||
| − | |||
| − | |||
| − | |||
| − | + | e.g. | |
| + | LOOKUP() returns | ||
| − | + | LOOKUP() returns | |
| − | |||
| − | |||
| − | |||
| − | |||
| − | + | *LOOKUP() function can be used for two forms - '''Vector Form''' and '''Array Form'''. | |
| + | *In '''Vector Form''', Calci looks for the specified value in one-row or one-column range and returns the value from same position in second one-row or one-column range. | ||
| + | *In '''Array Form''', Calci looks for the specified value in first row or column of an array, and returns the value from same position in last row or column of the array. | ||
| + | *'''lkup_val''' can be a number, text or logical value, or reference to cell containing number, text or logical value. | ||
| + | *'''lkup_vector''' can be one row or one column containing set of values or a reference to cells containing values. | ||
| + | *Values in '''lkup_vector''' should be arranged in ascending order to obtain correct results. | ||
| + | *Arguments containing text values entered as a part of command, should be enclosed in quotes (e.g. "JOHN"). | ||
| + | *Size of '''lkup_vector''' should be same as '''rslt_vector''', else Calci displays #NULL error message. | ||
| + | *If no match is found for '''lkup_val''' in the specified range, Calci displays the largest value which is <= '''lkup_val'''. | ||
| + | *LOOKUP function is not case-sensitive. | ||
| + | *If '''lkup_val''' is < 'smallest value in '''lkup_vector'''', Calci displays #NULL error message. | ||
| + | *For invalid arguments, Calci displays #NULL error message. | ||
| − | + | == Examples == | |
| + | Following table shows the use of LOOKUP function in '''Vector Form''' - | ||
| + | <div id="2SpaceContent" class="zcontent" align="left"> | ||
| − | + | {| id="TABLE3" class="SpreadSheet blue" | |
| + | |- class="even" | ||
| + | | class="sshl_f" |'''Quantity''' | ||
| + | | class=" " |'''Item''' | ||
| + | | class=" " | | ||
| − | + | |- class="odd" | |
| + | | class="sshl_f" |10 | ||
| + | | class=" " |Pen | ||
| + | | class=" " | | ||
| − | + | |- class="even" | |
| − | + | | class="sshl_f" |12 | |
| − | + | | class=" " |Pencil | |
| + | | class=" " | | ||
| − | + | |- class="odd" | |
| + | | class="sshl_f" |15 | ||
| + | | class=" " |Eraser | ||
| + | | class=" " | | ||
| − | + | |- class="even" | |
| − | + | | class="sshl_f" |25 | |
| − | + | | class=" " |Notebooks | |
| + | | class=" " | | ||
| − | + | |- class="odd" | |
| + | | class="sshl_f" |35 | ||
| + | | class=" " |Markers | ||
| + | | class=" " | | ||
| − | + | |} | |
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | + | =LOOKUP(15,A2:A6,B2:B6) : Looks up for '15' in range A2 to A6 and returns value from range B2 to B6 in the same row. <br />Returns '''Pencil''' as a result. | |
| + | =LOOKUP(A2,A2:A6,B2:B6) : Looks up for '15' in range A2 to A6 and returns value from range B2 to B6 in the same row. <br />Returns '''Pen''' as a result. | ||
| + | =LOOKUP(17,A2:A6,B2:B6) : As there is no exact match for '17', matches the nearest value '15' and returns value from range B2 to B6 in same row. <br />Returns '''Eraser''' as a result. | ||
| + | |||
| − | [ | + | Following examples show the use of LOOKUP function in '''Array Form''' - |
| + | =LOOKUP("B",["A","B","C"],[100,200,300]) : Looks up for 'B' in an array and returns value in last row of array in same column. <br />Returns '''200''' as a result. | ||
| + | =LOOKUP("cat",["A","B","C"],[100,200,300]) : As there is no exact match, Calci finds the largest value <= 'cat' ('C') in array and returns value in last row of array in same column. <br />Returns '''300''' as a result. | ||
| − | + | == See Also == | |
| − | + | *[[Manuals/calci/VLOOKUP | VLOOKUP]] | |
| − | + | *[[Manuals/calci/HLOOKUP | HLOOKUP]] | |
| − | + | *[[Manuals/calci/MATCH | MATCH]] | |
| − | + | == References == | |
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | + | *[*[http://en.wikipedia.org/wiki/String_(computer_science) String] | |
| − | + | *[http://en.wikipedia.org/wiki/Comparison_of_programming_languages_(string_functions)#Find Find String] | |
| − | |||
Revision as of 17:52, 13 February 2014
LOOKUP(lkup_val, lkup_vector, rslt_vector)
- where, lkup_val is the value to be matched from an array or range of values,
- lkup_vector is the array of values or reference to one row or one column containing values, and
- rslt_vector is a range that contains only one-row or one-column and is same size as lkup_vector.
LOOKUP() looks up for a specified value in one-row range or one-column range or an array, and displays a value from same position in second one-row range or one-column range or an array.
Description
LOOKUP(lkup_val, lkup_vector, rslt_vector)
e.g. LOOKUP() returns
LOOKUP() returns
- LOOKUP() function can be used for two forms - Vector Form and Array Form.
- In Vector Form, Calci looks for the specified value in one-row or one-column range and returns the value from same position in second one-row or one-column range.
- In Array Form, Calci looks for the specified value in first row or column of an array, and returns the value from same position in last row or column of the array.
- lkup_val can be a number, text or logical value, or reference to cell containing number, text or logical value.
- lkup_vector can be one row or one column containing set of values or a reference to cells containing values.
- Values in lkup_vector should be arranged in ascending order to obtain correct results.
- Arguments containing text values entered as a part of command, should be enclosed in quotes (e.g. "JOHN").
- Size of lkup_vector should be same as rslt_vector, else Calci displays #NULL error message.
- If no match is found for lkup_val in the specified range, Calci displays the largest value which is <= lkup_val.
- LOOKUP function is not case-sensitive.
- If lkup_val is < 'smallest value in lkup_vector', Calci displays #NULL error message.
- For invalid arguments, Calci displays #NULL error message.
Examples
Following table shows the use of LOOKUP function in Vector Form -
| Quantity | Item | |
| 10 | Pen | |
| 12 | Pencil | |
| 15 | Eraser | |
| 25 | Notebooks | |
| 35 | Markers |
=LOOKUP(15,A2:A6,B2:B6) : Looks up for '15' in range A2 to A6 and returns value from range B2 to B6 in the same row.
Returns Pencil as a result. =LOOKUP(A2,A2:A6,B2:B6) : Looks up for '15' in range A2 to A6 and returns value from range B2 to B6 in the same row.
Returns Pen as a result. =LOOKUP(17,A2:A6,B2:B6) : As there is no exact match for '17', matches the nearest value '15' and returns value from range B2 to B6 in same row.
Returns Eraser as a result.
Following examples show the use of LOOKUP function in Array Form -
=LOOKUP("B",["A","B","C"],[100,200,300]) : Looks up for 'B' in an array and returns value in last row of array in same column.
Returns 200 as a result.
=LOOKUP("cat",["A","B","C"],[100,200,300]) : As there is no exact match, Calci finds the largest value <= 'cat' ('C') in array and returns value in last row of array in same column.
Returns 300 as a result.