Changes

Manuals/calci/VLOOKUP (view source)

Revision as of 23:23, 15 February 2014

3,610 bytes removed , 23:23, 15 February 2014

no edit summary

Line 1: Line 1: −

~~<div id~~=~~"6SpaceContent" class="zcontent" align="left"> '''~~VLOOKUP~~'''~~(~~'''~~lkup_val~~'''~~,'''tb_arr''','''c_index_n''',range_lk)

+

=VLOOKUP(lkup_val, table_arr, column_index_num, range_lkup)=

−

~~~~'''~~where~~ lkup_val ~~is t~~'''~~he~~ value to ~~search in the~~ first column of the table ~~[javascript:AppendPopup(this~~,'~~xldefArray_1~~'~~) array][javascript:AppendPopup(this~~,'~~xldefArray_1~~'~~) ,]~~

+

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.

−

~~tb_arr is~~ the ~~two or more columns~~ of ~~data,~~

+

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.

−

~~c_index_n is the column number in table_array from which the matching value must be returned and ~~

+

== Description ==

−

~~range_lk is a logical value that specifies whether one should want VLOOKUP to find an exact match or an approximate match:~~

+

VLOOKUP(lkup_val, table_arr, column_index_num, range_lkup)

−

~~</div>~~

+

*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").

−

~~<div id="1SpaceContent" class="zcontent" align="left"> IT searches~~ 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~~. ~~</div>~~

−

~~----~~

−

~~<div id=~~"~~7SpaceContent~~" ~~class="zcontent" align="left">~~

−

* ~~ODDFYIELD shows~~ the ~~error~~ value, ~~when~~ '~~settle~~', 'M', 'I', or 'FC' ~~is not a valid date or~~ 'R' or '~~Yield~~' < 0.~~~~

+

*VLOOKUP function is not case-sensitive.

−

* ~~Basis must be between 0 and 4~~. ~~~~

+

*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.

−

* <~~font color~~="~~#000000~~"~~>M >FC > settle > I</font~~>

+

== Examples ==

+

Following table shows the use of VLOOKUP function in '''Vector Form''' -

+

−

~~ ~~

+

{| id="TABLE3" class="SpreadSheet blue"

+

|- class="even"

+

| class="sshl_f" |'''Number'''

+

| class=" " |'''Square'''

+

| class=" " |'''Cube'''

−

~~</div>~~

+

|- class="odd"

−

-~~---~~

+

| class="sshl_f" |1

−

~~<div id~~="~~12SpaceContent~~" class="~~zcontent~~" ~~align~~="~~left~~"~~><div~~ class="~~ZEditBox" align="left~~">

+

| class=" " |1

+

| class=" " |1

−

~~VLOOKUP~~

+

|- class="even"

+

| class="sshl_f" |2

+

| class=" " |4

+

| class=" " |8

−

~~</div></div>~~

+

|- class="odd"

−

~~---~~-

+

| class="sshl_f" |3

−

~~<div id~~="~~8SpaceContent~~" class="~~zcontent~~" ~~align~~="~~left~~">

+

| class=" " |9

+

| class=" " |27

−

~~Let's see an example~~

+

|- class="even"

+

| class="sshl_f" |4

+

| class=" " |16

+

| class=" " |64

−

~~[javascript:ToggleDiv('divExpCollAsst_1') C1 C2 C3]~~

+

|- class="odd"

+

| class="sshl_f" |5

+

| class=" " |25

+

| class=" " |125

−

~~[javascript:ToggleDiv('divExpCollAsst_1') R1 0.327 4.52 600]~~

+

|- class="even"

+

| class="sshl_f" |6

+

| class=" " |36

+

| class=" " |216

+

|}

−

~~[javascript~~:~~ToggleDiv(~~'~~divExpCollAsst_1~~') <~~font color="#666666"~~>~~~~R2~~ .~~423 4~~.~~21 500~~</~~font~~>~~]

+

=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.

+

== See Also ==

−

[~~javascript:ToggleDiv('divExpCollAsst_1') R3 0.517 3.87 400<~~/~~font><~~/~~font><~~/~~font>~~]

+

*[[Manuals/calci/LOOKUP | LOOKUP]]

+

*[[Manuals/calci/HLOOKUP | HLOOKUP]]

+

*[[Manuals/calci/MATCH | MATCH]]

−

~~[javascript:ToggleDiv('divExpCollAsst_1') R4 0.574 3.15 300]~~

+

== References ==

−

~~[javascript:ToggleDiv('divExpCollAsst_1') R5 0.643 2.98 200]~~

−

~~[javascript:ToggleDiv('divExpCollAsst_1') R6 0.711 2.75 150]~~

−

~~[javascript:ToggleDiv('divExpCollAsst_1') R7 0.857 2.5 100]~~

−

~~[javascript:ToggleDiv('divExpCollAsst_1') R8 2.04 .0.987 50]~~

−

~~[javascript:ToggleDiv('divExpCollAsst_1') R9 2.18 0.975 0]~~

−

~~[javascript:ToggleDiv('divExpCollAsst_1') <nowiki>=VLOOKUP(1,C1R1:C3R9,2) is 2.5</nowiki>]~~

−

~~[javascript:ToggleDiv('divExpCollAsst_1') ///rly =VLOOKUP(1,C1R1:C3R9,3,TRUE) is 100]~~

−

~~ ~~

−

~~</div>~~

−

~~----~~

−

~~<div id="10SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Syntax </div><div class="ZEditBox"><center></center></div></div>~~

−

~~----~~

−

~~<div id="4SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Remarks </div></div>~~

−

~~----~~

−

~~<div id="3SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Examples </div></div>~~

−

~~----~~

−

~~<div id="11SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Description </div></div>~~

−

~~----~~

−

~~<div id="5SpaceContent" class="zcontent" align="left">~~

−

~~{| id="TABLE1" class="SpreadSheet blue"~~

−

~~|- class="even"~~

−

~~| class=" " |~~

−

~~| class=" " | Column1~~

−

~~| class=" " | Column2~~

−

~~| class=" " | Column3~~

−

~~| class=" " | Column4~~

−

~~|- class="odd"~~

−

~~| class=" " | Row1~~

−

~~| class=" " | 0.327~~

−

~~| class="sshl_f" | 4.52~~

−

~~| class="sshl_f" | 600~~

−

~~| class="sshl_f" | 2.5~~

−

~~|- class="even"~~

−

~~| class=" " | Row2~~

−

~~| class="sshl_f" | .423~~

−

~~| class="sshl_f" | 4.21~~

−

~~| class="sshl_f" | 500~~

−

~~| class="sshl_f" | 100~~

−

~~|- class="odd"~~

−

~~| Row3~~

−

~~| class="sshl_f" | 0.517~~

−

~~| class="sshl_f" | 3.87~~

−

~~| class="sshl_f" | 400~~

−

~~| class="SelectTD sshl_f sshl_t ChangeBGColor" |~~

−

~~|- class="even"~~

−

~~| Row4~~

−

~~| class="sshl_f" | 0.574~~

−

~~| class="sshl_f" | 3.15~~

−

~~| class="sshl_f" | 300~~

−

~~| class=" sshl_t" |~~

−

~~|- class="odd"~~

−

~~| class=" " | Row5~~

−

~~| class="sshl_f" | 0.643~~

−

~~| class="sshl_f" | 2.98~~

−

~~| class="sshl_f" | 200~~

−

~~| class=" sshl_t" |~~

−

~~|- class="even"~~

−

~~| class="sshl_f" | Row6~~

−

~~| class="sshl_f" | 0.711~~

−

~~| class="sshl_f" | 2.75~~

−

~~| class="sshl_f" | 150~~

−

~~| class="sshl_f" |~~

−

~~|- class="odd"~~

−

~~| class="sshl_f" | Row7~~

−

~~| class="sshl_f" | 0.857~~

−

~~| class="sshl_f" | 2.5~~

−

~~| class="sshl_f" | 100~~

−

~~| class="sshl_f " |~~

−

~~|- class="even"~~

−

~~| class="sshl_f" | Row8~~

−

~~| class="sshl_f" | 2.04~~

−

~~| class="sshl_f" | 0.987~~

−

~~| class="sshl_f" | 50~~

−

~~| class="sshl_f" |~~

−

~~|- class="odd"~~

−

~~| class="sshl_f" | Row9~~

−

~~| class="sshl_f" | 2.18~~

−

~~| class="sshl_f" | 0.975~~

−

~~| class=" " | 0~~

−

~~| class=" sshl_f " |~~

−

|}

−

~~<div align="left">~~[[~~Image~~:~~calci1~~.~~gif]]<~~/~~div><~~/~~div>~~

+

*[http://en.wikipedia.org/wiki/Lookup_table#VLOOKUP VLOOKUP]

−

~~----~~

+

*[http://en.wikipedia.org/wiki/Comparison_of_programming_languages_(string_functions)#Find Find String]

Swapna

writer

2,661

edits