Lookups targeting merged cells - only returning value for first row

Question

I have a master worksheet which contains data that I wish to link to another 'summary' sheet using a lookup.

However, some of the cells whose data I wish to include in the summary sheet are merged across two or more adjacent rows. To be clear, the 'primary' column A that I am using in my formula in order to identify the target row does not contain merged cells, but the column from which I wish to return a value does.

I have tried VLOOKUP and INDEX+MATCH. The problem is that the data is only returned for the first row's key, and the others return zero (as though the cell in the target column were blank, where actually it is merged).

I have tried inelegant ways around this, e.g. using IF statements to try to find the top row of the merged cell. However, these don't work well if the order of values in the summary sheet is different from that in the master sheet, as well as being messy.

Can this be done?

Answer 1

I don't think there is any easy way to do this with existing Excel formulas. The problem is two-fold. First, AFAIK there is no Excel function that will tell you if a cell is part of a merged range. Second, the value shown in a merged ragne is actually only in the first cell of the merged range.

If you are willing to use VBA to create a custom function, this can be done fairly easily with a combination of the Match function and the fact that Range objects know if they are part of a merged range.

Public Function VLookupMerge(lookup_value As Variant, table_array As Range, col_index As Long) As Variant
   Dim sMatchFormula As String
   Dim row_index As Variant
   Dim r As Range

   sMatchFormula = "=MATCH(" & lookup_value _
                        & "," & table_array.Columns(1).Address(External:=True) _
                        & ",0)"
   row_index = Application.Evaluate(sMatchFormula)

   If TypeName(row_index) = "Error" Then
      VLookupMerge = row_index
   Else
      Set r = table_array.Cells(row_index, col_index)
      VLookupMerge = r.MergeArea.Range("A1")
      Set r = Nothing
   End If

End Function

Answer 2

Here is another way (using VBA) to know if a cell is part of a merged region. I sometimes use this function (and some others derived form this one) :

Public Function IsMerged(AnyRange As Range) As Boolean
   Dim MergedRegion As Range

   IsMerged = AnyRange.MergeCells
   If IsMerged Then Set MergedRegion = AnyRange.MergeArea Else Set MergedRegion = AnyRange
   Debug.Print MergedRegion.Rows.Count; " x "; MergedRegion.Columns.Count
   Debug.Print MergedRegion.Cells.Address

End Function

Hope this will help.

Answer 3

Merged cells are just formatting. The issue is correctly pointing to the cell that has the value. One approach would be a helper column where Excel can find what it needs (either the actual value or a pointer to it). This is a lot more practical if every row is associated with either an actual value or a "merged value". If some rows can have no value, it gets more complicated to create a roadmap in the helper column.

But for a case where every row is associated with either an actual or merged value, a simple solution would be something like this. Say the VLOOKUP target is column A and column B contains merged values. Create a helper column to the right; I'll use column C for the example. Lets assume row 1 is the first data row, so B1 will always have a value. In the helper column, C1 would just be:

    =B1

Cell C2 would be:

    =if(isblank(B2),C1,B2)

Copy that down the column. The helper column would then contain a value for every row and the VLOOKUP can pull from that column.

I'm not aware of a regular (at least native), function that can return whether a cell is part of a merged range. So, if there can be cells with neither an actual or merged value, one of the VBA solutions would probably be required.