Difference between revisions of "Manuals/calci/HLOOKUP"
Jump to navigation
Jump to search
| Line 1: | Line 1: | ||
=HLOOKUP(lkup_val, table_arr, row_index_num, range_lkup)= | =HLOOKUP(lkup_val, table_arr, row_index_num, range_lkup)= | ||
| + | <div style="font-size:30px">'''HLOOKUP (Value,Array,Column,ApproximateMatchFlag) '''</div><br/> | ||
where, | where, | ||
| − | *''' | + | *'''Value''' is the value to be matched from an array or top row of the table, |
| − | *''' | + | *'''Array''' refers to two or more rows of data values, |
| − | *''' | + | *'''Column''' is the row number in the table from which the matching value should be returned, and |
| − | *''' | + | *'''ApproximateMatchFlag''' is an optional logical value that specifies whether to find an exact match or approximate match of the mentioned lookup value. |
| − | HLOOKUP() looks | + | HLOOKUP(), looks in the top row of an array and returns the value of the indicated cell. |
== Description == | == Description == | ||
| − | HLOOKUP( | + | HLOOKUP (Value,Array,Column,ApproximateMatchFlag) |
*The 'H' in HLOOKUP stands for Horizontal. This function looks the value row wise. | *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"). | *Arguments containing text values entered as a part of command, should be enclosed in quotes (e.g. "JOHN"). | ||
*HLOOKUP function is not case-sensitive. | *HLOOKUP function is not case-sensitive. | ||
| − | *If ''' | + | *If '''Value''' is less than 'smallest value in top row of table', Calci displays #NULL error message. |
| − | *If ''' | + | *If '''Column''' is 1, Calci returns the value from first row in table array. If '''Column''' is 2, Calci returns the value from second row in table array and so on. |
| − | *For ''' | + | *For '''Column''' < 1 or '''Column''' > 'number of rows in array', Calci returns #NULL error message. |
*The values in first row should be arranged in ascending order for correct results. | *The values in first row should be arranged in ascending order for correct results. | ||
| − | *Argument ''' | + | *Argument '''ApproximateMatchFlag''' can be TRUE or FALSE. |
| − | *If ''' | + | *If '''ApproximateMatchFlag''' is TRUE or Omitted, Calci returns exact or approximate match of the value. |
*Approximate value is the largest value that is <= '''lkup_val'''. | *Approximate value is the largest value that is <= '''lkup_val'''. | ||
| − | *If ''' | + | *If '''ApproximateMatchFlag''' 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. | *For invalid arguments, Calci displays #NULL error message. | ||
Revision as of 14:16, 9 August 2018
HLOOKUP(lkup_val, table_arr, row_index_num, range_lkup)
HLOOKUP (Value,Array,Column,ApproximateMatchFlag)
where,
- Value is the value to be matched from an array or top row of the table,
- Array refers to two or more rows of data values,
- Column is the row number in the table from which the matching value should be returned, and
- ApproximateMatchFlag is an optional logical value that specifies whether to find an exact match or approximate match of the mentioned lookup value.
HLOOKUP(), looks in the top row of an array and returns the value of the indicated cell.
Description
HLOOKUP (Value,Array,Column,ApproximateMatchFlag)
- 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 Value is less than 'smallest value in top row of table', Calci displays #NULL error message.
- If Column is 1, Calci returns the value from first row in table array. If Column is 2, Calci returns the value from second row in table array and so on.
- For Column < 1 or Column > '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 ApproximateMatchFlag can be TRUE or FALSE.
- If ApproximateMatchFlag is TRUE or Omitted, Calci returns exact or approximate match of the value.
- Approximate value is the largest value that is <= lkup_val.
- If ApproximateMatchFlag 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.
Related Videos
See Also
References