Retrieving Values From Excel Merged Columns

Question

I'm doing a hlookup against a value that spans multiple columns. My data is similar to this:

      A      B      C      D 
  ---------------------------  
1|       Col1          Col2
2|     x      y      z      w
3|
4|

In rows 3 and 4 (A3, B3, C3, D3, etc.), I'd like to put formulas that will do an hlookup somewhere else in the workbook. The trick is, I'd like it to look up "Col1" for columns A and B and "Col2" for columns C and D. "Col1" is in A1, but is really A1 and B1 merged. When I reference A1, "Col1" appears, but when I reference B1, the return value is blank.

Any ideas?

Answer 1

Cells B1 and D2 contain no values, only A1 and C1 have something inside them.

So you'll just have to make sure that your formulas in columns A and B both refer to A1 as the lookup value, and that your formulas in columns C and D both refer to C1 for the lookup value.

Answer 2

To get access to the "Col1" and "Col2" labels, you can use the following:

=INDEX($1:$1,1,COLUMN()-MOD(COLUMN()-1,2))

Note: This assumes that you are grouping together the same number of cells. If it were three cells, you would just change the last number in the formula to a 3, and so on.

Edit: Here's how it works:

INDEX($1:$1,1, x ) returns the value of the cell in row 1, column x. If your table is not actually located in the top left corner of the worksheet, you can change this to the actual range that includes all of your merged labels. In this case, it would be: INDEX($A$1:$D$1,1, x )

COLUMN() returns the column number of the current cell (1 in column A, 2 in column B, etc.)

MOD(COLUMN()-1,x) returns an offset from the current column to the column that holds the proper label

Answer 3

I've built a simple function in vba that will solve this problem:

Function mergedText(rngMergedCell As Range)

If rngMergedCell.MergeCells = True Then
    mergedText = rngMergedCell.MergeArea(1, 1)
Else
    mergedText = rngMergedCell
End If

End Function

If the cell is a merged cell, the function will return the value in the first element of the merged cell - this is where the merged cell stores its value