Changes

Jump to navigation Jump to search

Manuals/calci/HLOOKUP (view source)

Revision as of 00:13, 16 February 2014

964 bytes removed ,  00:13, 16 February 2014
no edit summary
Line 1: Line 1: −
<div id="6SpaceContent" class="zcontent" align="left"><font face="Arial, sans-serif"><font size="2">'''HLOOKUP'''</font></font><font face="Arial, sans-serif"><font size="2">(lk</font></font><font face="Arial, sans-serif"><font size="2">'''_val'''</font></font><font face="Arial, sans-serif"><font size="2">,</font></font><font face="Arial, sans-serif"><font size="2">'''tb_arr'''</font></font><font face="Arial, sans-serif"><font size="2">,</font></font><font face="Arial, sans-serif"><font size="2">'''rw_index_num'''</font></font><font face="Arial, sans-serif"><font size="2">,range_lk)</font></font>
+
=HLOOKUP(lkup_val, table_arr, row_index_num, range_lkup)=
   −
<font face="Arial, sans-serif"><font size="2">'''where lk_val '''</font></font><font face="Arial, sans-serif"><font size="2">is the value to be found in the first row of the table ,</font></font>
+
where,
 +
*'''lkup_val''' is the value to be matched from an array or top row of the table,
 +
*'''table_arr''' refers to two or more rows of data values,
 +
*'''row_index_num''' is the row number in the table from which the matching value should be returned, and
 +
*'''range_lkup''' is an optional logical value that specifies whether to find an exact match or approximate match of the mentioned lookup value.
   −
<font face="Arial, sans-serif"><font size="2">tb_arr is a table of information in which data is looked up,</font></font>
+
HLOOKUP() looks up for a value in the top row of a table and returns a value in the same column from specified row in the table.
   −
<font face="Arial, sans-serif"><font size="2">'''rw_index '''</font></font><font face="Arial, sans-serif"><font size="2">is the row number in tb_arr from which the matching value will be returned and </font></font>
+
== Description ==
   −
<font face="Arial, sans-serif"><font size="2">range_lk is a logical value that specifies whether one wants HLOOKUP to find an exact match or an approximate match. </font></font>
+
HLOOKUP(lkup_val, table_arr, row_index_num, range_lkup)
   −
</div>
+
*The 'H' in HLOOKUP stands for Horizontal. This function looks the value row 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"> <font face="Arial, sans-serif"><font size="2">It searches for a value in the top row of a table or an </font></font><u>[javascript:AppendPopup(this,'xldefArray_1') <font face="Arial, sans-serif"><font size="2" color="#0000ff">array </font></font>]</u><font face="Arial, sans-serif"><font size="2">of values.</font></font></div>
+
*HLOOKUP function is not case-sensitive.
−
----
+
*If '''lkup_val''' is less than 'smallest value in top row of table', Calci displays #NULL error message.
−
<div id="12SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="left">
+
*If '''row_index_num''' is 1, Calci returns the value from first row in table array. If '''row_index_num''' is 2, Calci returns the value from second row in table array and so on.
 +
*For '''row_index_num''' &lt; 1 or '''row_index_num''' &gt; 'number of rows in array', Calci returns #NULL error message.
 +
*The values in first row 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 &lt;= '''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.
   −
HLOOKUP
+
== Examples ==
−
 
+
Following table shows the use of HLOOKUP function -
−
</div></div>
  −
----
  −
<div id="8SpaceContent" class="zcontent" align="left"> 
  −
 
  −
<font face="Arial, sans-serif"><font size="2">Let's see an example</font></font>
  −
 
  −
<font face="Arial, sans-serif"><font size="2">C1 C2 C3</font></font>
  −
 
  −
<font face="Arial, sans-serif"><font size="2">R1 Orange Apple Lemon</font></font>
  −
 
  −
<font face="Arial, sans-serif"><font size="2">R2 6 10 5</font></font>
  −
 
  −
<font face="Arial, sans-serif"><font size="2">R3 4 3 9</font></font>
  −
 
  −
<font face="Arial, sans-serif"><font size="2">R4 8 7 12</font></font>
  −
 
  −
<font face="Arial, sans-serif"><font size="2"><nowiki>=HLOOKUP(“Orange”,C1R1:C3R4,2,TRUE) is 5</nowiki></font></font>
  −
 
  −
<font face="Arial, sans-serif"><font size="2">///rly =HLOOKUP(“Apple”,C1R1:C3R4,3,FALSE) is 3</font></font>
  −
 
  −
</div>
  −
----
  −
<div id="10SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Syntax </div><div class="ZEditBox"><center></center></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="2SpaceContent" class="zcontent" align="left">
 
<div id="2SpaceContent" class="zcontent" align="left">
    
{| id="TABLE3" class="SpreadSheet blue"
 
{| id="TABLE3" class="SpreadSheet blue"
 
|- class="even"
 
|- class="even"
−
| class=" " |
+
| class="sshl_f" |Apple
−
| Column1
+
| class=" " |Banana
−
| class="   " | Column2
+
| class=" " |Mango
−
| Column3
+
| class=" " |Orange
−
| Column4
+
| class=" " |Strawberry
 +
 
 +
 
 
|- class="odd"
 
|- class="odd"
−
| class=" " | Row1
+
| class="sshl_f" |Red
−
| class="sshl_f  " | Orange
+
| class=" " |Yellow
−
| class="sshl_f" | apple
+
| class=" " |Yellow
−
| class="sshl_f" | Lemon
+
| class=" " |Orange
−
| class="                 sshl_f  " |
+
| class=" " |Red
 +
 
 
|- class="even"
 
|- class="even"
−
| class=" " | Row2
+
| class="sshl_f" |20
−
| class="sshl_f" | 6
+
| class=" " |14
−
| class="sshl_f" | 10
+
| class=" " |20
−
| class="sshl_f" | 5
+
| class=" " |40
−
| class=" " |
+
| class=" " |35
 +
 
 
|- class="odd"
 
|- class="odd"
−
| Row3
+
| class="sshl_f" |
−
| class="sshl_f" | 4
  −
| class="sshl_f" | 3
  −
| class="sshl_f" | 9
  −
| class=" " |
  −
|- class="even"
  −
| Row4
  −
| class="sshl_f" | 8
  −
| class="sshl_f" | 7
  −
| class=" " | 12
   
| class=" " |
 
| class=" " |
−
|- class="odd"
  −
| class=" " | Row5
  −
| class="sshl_f" | 5
  −
| class="                                                                                                                                  " |
   
| class=" " |
 
| class=" " |
−
| class="    " |
  −
|- class="even"
  −
| Row6
  −
| class="sshl_f" | 3
   
| class=" " |
 
| class=" " |
 
| class=" " |
 
| class=" " |
−
| class="  SelectTD  SelectTD" |
+
 
−
<div id="2Space_Handle" title="Click and Drag to resize CALCI Column/Row/Cell. It is EZ!"></div><div id="2Space_Copy" title="Click and Drag over to AutoFill other cells."></div>
   
|}
 
|}
   −
<div align="left">[[Image:calci1.gif]]</div></div>
+
=HLOOKUP("Banana",A1:E3,2) : Looks up for 'Banana' in first row and returns value from second row in the same column. <br />Returns '''Yellow''' as a result.
−
----
+
=HLOOKUP(C1,A1:E3,3,FALSE) : Looks up for exact match for 'Mango' in first row and <br />returns value from third row in the same column. Returns '''20''' as a result.
 +
=HLOOKUP("P",A1:E3,3,TRUE) : Looks up for an approximate match for 'P' in first row, <br />matches the nearest value 'Orange' and returns value from third row in same column. Returns '''40''' as a result.
 +
 +
== See Also ==
 +
 
 +
*[[Manuals/calci/LOOKUP | LOOKUP]]
 +
*[[Manuals/calci/VLOOKUP | VLOOKUP]]
 +
*[[Manuals/calci/MATCH | MATCH]]
 +
 
 +
== References ==
 +
 
 +
*[http://en.wikipedia.org/wiki/Lookup_table#HLOOKUP HLOOKUP]
 +
*[http://en.wikipedia.org/wiki/Comparison_of_programming_languages_(string_functions)#Find Find String]
Swapna
writer
2,661

edits

Retrieved from "http://wiki.zcubes.com/Special:MobileDiff/197844"

Navigation menu