Data anlysis using Hlookup in Excel
Generally the price of a product changes with time. This may be due increase in Raw material costs, interest costs, processing costs, salaries, etc. Sometimes governments can change their policies on the amount of VAT (Value added Tax) to be charged. How can we use Hlookup to get the correct item price from a large amount of data for a relevant date and use it in our calculations?
Syntax of Hlookup: Hlookup(lookup_value, table_array, row_index_num, [range_lookup])
Lookup_value: this value is required and is to be found in the first row of the table. Lookup_value can be a value, a reference (cell address like B8), or a text string. The latter must be enclosed in quotes.
Table_array: This value is also required and is generally a range of cells in which data is looked up. You can select the range manually or use a name like price_data as in our case.
Row_index_num: This value must be entered and it is the row number in table_array from which the matching value will be returned. A row_index_num of 1 returns the first row value in table_array, a row_index_num of 2 returns the second row value in table_array, and so on. If row_index_num is less than 1, HLOOKUP returns the #VALUE! error value; if row_index_num is greater than the number of rows on table_array, HLOOKUP returns the #REF! error value.
Range_lookup: This value is optional and is a logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, HLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.