HLOOKUP, or Horizontal Lookup, is a function in Microsoft Excel that is used to search for a specific value in the top row of a table and return a value from a specified row in the same column. This function is commonly used to search for and retrieve data from large tables of data, such as employee records or sales data.
The HLOOKUP function has four required arguments and one optional argument. The first argument is the value that you want to search for, also known as the “lookup value”. The second argument is the range of cells that contain the data you want to search through, also known as the “table array”. The third argument is the row number of the table array that contains the value you want to return, also known as the “row index number”. The fourth argument is a logical value that determines whether the lookup value should be matched exactly or if it should be matched approximately, also known as the “range lookup” value. The optional fifth argument is the number of the column in the table array that contains the value you want to return, also known as the “column index number”.
To use the HLOOKUP function, you must first select the cell where you want the returned value to be displayed. Then, type “=HLOOKUP” followed by the required arguments in parentheses. For example, if you want to search for the value “John” in a table array of employee names and return the employee’s salary from row 3, you would type “=HLOOKUP(“John”, A1:C10, 3, TRUE)” into the selected cell. In this example, the lookup value is “John”, the table array is A1:C10, the row index number is 3, and the range lookup value is TRUE, which indicates that the lookup value should be matched approximately.
One important thing to note about the HLOOKUP function is that it only searches for values in the top row of the table array. This means that if you want to search for a value in a different row, you must first rearrange the table so that the row you want to search is at the top. For example, if you want to search for the employee’s salary in the above example but the salary is in the second row of the table array, you would need to rearrange the table so that the salary is in the first row before using the HLOOKUP function.
Another important thing to note is that the HLOOKUP function is case-insensitive, meaning it will not differentiate between uppercase and lowercase letters when searching for the lookup value. For example, if the lookup value is “John” and the table array contains “john”, the HLOOKUP function will still return the correct value.
There are a few different ways you can use the HLOOKUP function to return values from different rows in the table array. If you want to return a value from the same row as the lookup value, you can use the MATCH function in conjunction with HLOOKUP. The MATCH function searches for a specific value in a range of cells and returns the position of that value in the range. For example, if you want to search for the employee’s salary in the above example but the employee’s name is not in the top row of the table array, you could use the MATCH function to find the position of the employee’s name in the table array and then use that position as the row index number in the HLOOKUP function.