Using HLOOKUP for Merged Cells in Excel
HLOOKUP is a function that allows you to look up values in a table and return the corresponding value from another column. However, HLOOKUP can be tricky to use when the table contains merged cells.
We will show you how to use HLOOKUP with merged cells.
To use HLOOKUP with merged cells, you’ll need to utilize a combination of the OFFSET and INDIRECT functions. These functions will allow you to navigate the merged cells effectively and retrieve the data you need.
Identify the Merged Cell
Begin by identifying the merged cell that contains the value you want to look up. Merged cells are often used for formatting purposes, such as creating headers that span multiple columns.
Create a New Column
Next, create a new column next to the column with the merged cells. This new column will be used to perform the necessary calculations.
Use the OFFSET Function
In the new column, use the OFFSET function to return a reference to the first cell within the merged cell range. The OFFSET function takes three arguments:
- Reference: The starting cell from which you want to offset.
- Rows: The number of rows to offset from the reference cell.
- Columns: The number of columns to offset from the reference cell.
The formula to use the OFFSET function would look like this:
=OFFSET(Reference_Cell, 0, 0)
Replace “Reference_Cell” with the cell reference that contains the merged cells.
Use the INDIRECT Function
Now, use the INDIRECT function to convert the reference obtained from the OFFSET function into a text string. The INDIRECT function takes a text string and treats it as a cell reference. You can use it as follows:
=INDIRECT(“A” & Row_Number)
In this formula, “A” represents the column containing the merged cells, and “Row_Number” is the row number where you have placed the OFFSET function. This combination generates a text string resembling a cell reference.
Apply HLOOKUP
With the reference text string prepared using the INDIRECT function, you can now apply HLOOKUP. The HLOOKUP function takes four arguments:
- Lookup_Value: The value you want to find in the row with merged cells.
- Table_Array: The range of cells where you want to perform the lookup.
- Row_Index_Num: The relative row number within the table array where the value should be located.
- Range_Lookup: A logical value indicating whether you want an approximate or exact match. Use “FALSE” for an exact match.
Your HLOOKUP formula should look like this:
=HLOOKUP(Lookup_Value, INDIRECT(“A” & Row_Number), Row_Index_Num, FALSE)
Ensure that you replace “Lookup_Value” with the specific value you’re searching for and “Row_Index_Num” with the desired row index number within the table array.
