Difference between revisions of "Manuals/calci/HLOOKUP"
Jump to navigation
Jump to search
(Created page with "<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">(l...") |
|||
| Line 1: | Line 1: | ||
| − | + | =HLOOKUP(lkup_val, table_arr, row_index_num, range_lkup)= | |
| − | + | 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. | ||
| − | + | 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. | |
| − | + | == Description == | |
| − | + | HLOOKUP(lkup_val, table_arr, row_index_num, range_lkup) | |
| − | + | *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"). | |
| − | + | *HLOOKUP function is not case-sensitive. | |
| − | + | *If '''lkup_val''' is less than 'smallest value in top row of table', Calci displays #NULL error message. | |
| − | + | *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''' < 1 or '''row_index_num''' > '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 <= '''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 HLOOKUP function - | |
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
<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 |
| − | | | + | | class=" " |Banana |
| − | | class=" | + | | class=" " |Mango |
| − | | | + | | class=" " |Orange |
| − | | | + | | class=" " |Strawberry |
| + | |||
| + | |||
|- class="odd" | |- class="odd" | ||
| − | | class=" " | | + | | class="sshl_f" |Red |
| − | | class=" | + | | class=" " |Yellow |
| − | | class=" | + | | class=" " |Yellow |
| − | | class=" | + | | class=" " |Orange |
| − | | class=" | + | | class=" " |Red |
| + | |||
|- class="even" | |- class="even" | ||
| − | | class=" | + | | class="sshl_f" |20 |
| − | | class=" | + | | class=" " |14 |
| − | | class=" | + | | class=" " |20 |
| − | | class=" | + | | class=" " |40 |
| − | | class=" " | | + | | class=" " |35 |
| + | |||
|- class="odd" | |- class="odd" | ||
| − | + | | class="sshl_f" | | |
| − | |||
| − | |||
| − | | class="sshl_f" | | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| class=" " | | | class=" " | | ||
| − | |||
| − | |||
| − | |||
| − | |||
| class=" " | | | class=" " | | ||
| − | |||
| − | |||
| − | |||
| − | |||
| class=" " | | | class=" " | | ||
| class=" " | | | class=" " | | ||
| − | + | ||
| − | |||
|} | |} | ||
| − | < | + | =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] | ||
Revision as of 18:13, 15 February 2014
HLOOKUP(lkup_val, table_arr, row_index_num, range_lkup)
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.
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.
Description
HLOOKUP(lkup_val, table_arr, row_index_num, range_lkup)
- 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").
- HLOOKUP function is not case-sensitive.
- If lkup_val is less than 'smallest value in top row of table', Calci displays #NULL error message.
- 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 < 1 or row_index_num > '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 <= 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 HLOOKUP function -
| Apple | Banana | Mango | Orange | Strawberry
|
| Red | Yellow | Yellow | Orange | Red |
| 20 | 14 | 20 | 40 | 35 |
=HLOOKUP("Banana",A1:E3,2) : Looks up for 'Banana' in first row and returns value from second row in the same column.
Returns Yellow as a result.
=HLOOKUP(C1,A1:E3,3,FALSE) : Looks up for exact match for 'Mango' in first row and
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,
matches the nearest value 'Orange' and returns value from third row in same column. Returns 40 as a result.