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?

Solution 1

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

Solution 2

Here is another solution that can also work when the merged cells are of different widths, let me illustrate with an example:

1. Open a fresh Excel, merge B1, C1, D1
2. Type Col1 in the merged cell
3. In B2, type formula =B1, and in C2 =C1, in D2 =D1
4. You should see B2 to be Col1 while C2, D2 are 0
5. In B3, type the formula =A3, copy it
6. Right-click the merged cell B1:D1, select "paste special -> formulas"
7. You should see the merged cell being 0
8. Type Col1 in the merged cell
9. You should now see all B2, C2, D2 to be Col1, i.e. now you can reference the merged cell as you expect it to be.

If you can multiple merged cells, each of different widths, just paste the formula to all of them in one go.

The reason behind this works is because of a perculier design choice by Microsoft. It seems that when you paste formulas in merged cells, each underlying cell receives the formula (in contrast, if you enter a value, only the top-left cell gets it) So you can use it at your advantage and paste a formula that reference the cell next to it, and then overwrite the top-left cell with the value you want, then every cell underlying the merged cell will have that value.

Solution 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

Solution 4

A more generic variant of e.James's proposal is :

={INDEX($A$1:A1, 1, MAX(NOT(ISBLANK($A$1:A1))*COLUMN($A$1:A1)-COLUMN($A$1)+1))}

This relies on the fact that the merged cells are empty except for the first one (unless you are in a case like Martin's proposal).

Note: The curly braces are there to mark an array formula (do not enter them, just press alt+return to validate the formula in the cell).

Solution 5

I realize I am late to this thread but I found a really simple answer to this.

If, for example, your label is merged across 4 columns a1:d1, and if you reference b1, you will return "". For dynamically finding the right labels use this fx in your new table:

=if(OriginalTable!B1="",ThisTable!A1,OriginalTable!B1)

I am sure you will realize that this will capture ranges in e1:h1 etc as you drag across.

That's it. Hope it helps someone.

View more solutions

Comments

[Fionnuala]

Is the offset always -1 if the cell is null?

[cLFlaVA]

In the real spreadsheet, there are 5 cells merged together. I would need those five columns to reference the merged cell. Make sense?

[cLFlaVA]

I wanted to avoid this, as there are actually 132 columns of 6 merged cells (6 * 22). I wanted a copyable formula which will make it easy in the event that the formula ever needs to change.

[Mathias]

Thank you :) I like that "syntactic sugar" of yours

[AticusFinch]

If that assumption does not hold, the solution by James Hole is better as is completely generic.

[tbone]

This function is very slick, but for some reason I am finding that sometimes it doesn't work, and at other times it can get into a "state" where it was formerly working, but then stops. (Not very helpful but hoping maybe someone else had encountered similar anomalies?)

[RPh_Coder]

This is a fantastic answer that does not require additional functions/macros, etc.

[rkagerer]

Neat trick. TLDR is you use Paste Formulas to disperse =<cell to my left> into each cell in the span, then type a value into the first one.

[rkagerer]

Couple minor caveats to note: Inserting a new column inside the span won't replicate the adjacent formulas as you might expect (so you'll need to reapply these gymnastics). And versions of Excel prior to 2010 have a bug that unmerging the cell, then clicking Undo, doesn't restore the "dispersed" formulas.

[Clement]

Nice trick! Doesn't seem to work in Google Sheets.