writer
2,661
edits
Changes
Jump to navigation
Jump to search
Line 2:
Line 2: − + Line 16:
Line 16: − + Line 26:
Line 26: − + Line 66:
Line 66: − + − + − + − + Line 83:
Line 83: − *[*[http://en.wikipedia.org/wiki/String_(computer_science) String]+
no edit summary
*where, '''lkup_val''' is the value to be matched from an array or range of values,
*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
*'''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.
*'''rslt_vector''' is a range that contains only one-row or one-column and is same size as lkup_vector.
LOOKUP() returns
LOOKUP() returns
*LOOKUP() function can be used for two forms - '''Vector Form''' and '''Array Form'''.
*LOOKUP() function can be used in 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 '''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.
*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.
*If no match is found for '''lkup_val''' in the specified range, Calci displays the largest value which is <= '''lkup_val'''.
*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.
*LOOKUP function is not case-sensitive.
*If '''lkup_val''' is < 'smallest value in '''lkup_vector'''', Calci displays #NULL error message.
*If '''lkup_val''' is less than 'smallest value in '''lkup_vector'''', Calci displays #NULL error message.
*For invalid arguments, Calci displays #NULL error message.
*For invalid arguments, Calci displays #NULL error message.
|}
|}
=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(12,A2:A6,B2:B6) : Looks up for '12' 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(A2,A2:A6,B2:B6) : Looks up for '10' 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.
=LOOKUP(17,A2:A6,B2:B6) : As there is no exact match for '17', matches the nearest value '15' and <br />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''' -
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("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.
=LOOKUP("cat",["A","B","C"],[100,200,300]) : As there is no exact match, Calci finds the largest value <= 'cat' ('C') in array and <br />returns value in last row of array in same column. Returns '''300''' as a result.
== See Also ==
== See Also ==
== References ==
== References ==
*[http://en.wikipedia.org/wiki/String_(computer_science) String]
*[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]