The HLOOKUP function is similar to the VLOOKUP function, except it searches horizontal from left to right across the top row of a cell range until it finds the value you specify. When it finds the value it then looks down the column to find the value you specified. Because of the way data is typically structured, the HLOOKUP function is not used as commonly as VLOOKUP.
- Click in the cell where you want to type a search value.
- Enter a value to search for.
This can be any value from the first row. It’s only being used to test the formula and will be changed later. It’s best to pick a value in view so you can quickly see if you’ve entered the formula correctly.
- Click or press Enter.
- Click the cell where you want to add the HLOOKUP function.
- Click the Insert Function button.
The Insert Function dialog box opens.
- Select the HLOOKUP function.
If you don’t see HLOOKUP in the list, select Lookup & Reference in the menu below the search field and scroll to HLOOKUP in the list.
- Click OK.
The Function Arguments dialog box displays, where you’ll enter all the information needed to return the correct value. The HLOOKUP function has three required arguments.
- Fill in the function arguments.
- Lookup_value: The value to be found in the first column. It can be a value, a reference, or a text string.
- Table_array: The entire cell range or table from which data will be retrieved.
- Col_index_num: The column number in the table_array from which a matching value should be returned. The first column in the table or range is column 1.
- Range_lookup: This is an optional argument. Add “True” to find the closest match to the lookup value, or “False” to only find an exact match.
The HLOOKUP function is added in the selected cell. It will use the value you entered in step 2. Check to make sure the right value is being returned.
The cell containing the HLOOKUP function updates to return a value for the new lookup value you’ve entered.