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?

Delightful

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

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

Answer 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

Tragacanth

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

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

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?) – Pothead

Answer 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.

Tittup

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. – Tapis

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. – Tapis

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

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

Kephart

Answer 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).

Doublecheck

Answer 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.

Glossator

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

Answer 6

With the new dynamic reference, there are more options now. Here is a generic function I wrote that will search to the left of the cell and return the first value. It is NOT optimized, but it does the job for me.

=LET(
  TargetCell, A1,
  TargetRow, ROW(TargetCell),
  TargetCol, COLUMN(TargetCell),
  RowReference, INDIRECT(TargetRow & ":" & TargetRow),
  RowValues, TRANSPOSE(FILTER(RowReference,ISBLANK(RowReference)=FALSE)),
  RowValueColumns, MATCH(RowValues, RowReference,0),
  ReturnColumn, MAX(FILTER(RowValueColumns,RowValueColumns<=TargetCol)),
  Return, INDIRECT(ADDRESS(TargetRow,ReturnColumn)),

  Return
)

Strafe

This is the best answer for me as it allows merges to be of variable size. I know this question asked specifically about merged columns, but perhaps it would be good to append at the bottom a note with the minor changes needed to make it work with rows. – Torgerson

Answer 7

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.

Languedoc

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. – Delightful