HLOOKUP function in excel is a lookup function. This function is used to return data from a row in a table. Here ‘H’ stands for horizontal as the function moves horizontally to right in a row to lookup a value. It is used in three ways- Exact value, approximate value, Wildcard value. It is used when comparison values are located in the top row of a table. For vertical lookup VLOOKUP function is used.
=HLOOKUP (value, table, row_index, range_lookup)
- value – The value in the first row which is to be looked up. It can be a reference, value or a text string.
- table – The table which contains the data from where value is to be looked up.
- row_index – Row number.
- range_lookup – A logic which indicates an exact match, an approximate match. By default its value is TRUE which returns an approximate match.
- Range_lookup controls whether value needs to be precise or not. The default is TRUE = permit non-correct match.
- Set range_lookup to FALSE if a correct match is required.
- If range_lookup is TRUE (the default setting), a non-correct match will cause the HLOOKUP function to look for the closest value in the table that is less than the value.
- At the point when range_lookup is discarded, the HLOOKUP function will permit a non-correct match.
- In the event that range_lookup is FALSE (require correct match), values in the first row of table need not be sorted.
In this example, a value from table is retrieved into Column I by using the formula
=HLOOKUP(4,A3:G5,2,FALSE), Here price of order id number 4 is found from table (A:G), and FALSE is used to find the exact match.