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