Understanding HLOOKUP in Excel
- HLOOKUP stands for Horizontal Lookup and is a useful function in Microsoft Excel.
- This function allows you to search for a value in the first row of a specified table and return a corresponding value from a subsequent row.
- While less common than its vertical counterpart, VLOOKUP, HLOOKUP can be effective in specific scenarios where your data is arranged horizontally.
Syntax of HLOOKUP
- To use HLOOKUP, you need to understand its syntax: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).
- Here’s a breakdown of the parameters:
- lookup_value: The value you want to search for in the top row.
- table_array: The range of cells containing the data.
- row_index_num: The row number (starting from 1) from which to return the value.
- range_lookup: Set to FALSE for exact matches or TRUE for approximate (default).
Example Usage of HLOOKUP
Consider an example where you have data laid out horizontally:
| SR Number | A | B | C |
|---|---|---|---|
| 1 | Name | Dinesh | Manish |
| 2 | Marks | 80 | 90 |
| 3 | Grade | B | A |
- To find the marks of Bob, you would use the formula: =HLOOKUP(“Manish”, A1:C3, 2, FALSE). This will return a result of 90, which is from the second row of Manish’s column.
- Be mindful that HLOOKUP searches horizontally only in the first row of your data.
- For more dynamic formulas, consider utilizing INDEX and MATCH or the new XLOOKUP function available in Excel 365 and Excel 2021.