Manuals/calci/HLOOKUP

From ZCubes Wiki
Revision as of 13:29, 13 May 2015 by Swapna (talk | contribs)
Jump to navigation Jump to search

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.

Related Videos

HLOOKUP

See Also

References