Geek Answers Handbook

Retrieving values ​​from merged Excel columns

I am doing hlookup against a value that spans multiple columns. My details are similar to the following:

  ABCD --------------------------- 1| Col1 Col2 2| xyzw 3| 4|

On lines 3 and 4 (A3, B3, C3, D3, etc.) I would like to put formulas that will do hlookup somewhere else in the book. The trick is that I would like to see "Col1" for columns A and B and "Col2" for columns C and D. "Col1" is in A1, but in fact A1 and B1 merge. When I refer to A1, "Col1" appears, but when I refer to B1, the return value is empty.

Any ideas?

+4
source share
6 answers

To access the “Col1” and “Col2” labels, you can use the following:

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

Note. It is assumed that you are grouping the same number of cells. If it were three cells, you would just change the last number in the formula to 3, etc.

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 upper left corner of the worksheet, you can change it to the actual range, including all of your combined shortcuts. In this case it will 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 the offset from the current column to the column that contains the corresponding label

+7
source

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

  • Open a new Excel, merge B1, C1, D1
  • Enter Col1 in the merged cell
  • In B2, enter the formula = B1 , and in C2 = C1 , in D2 = D1
  • You should see B2 as Col1 , and C2, D2 - 0
  • In B3, enter the formula = A3 , copy it
  • Right-click merged cell B1: D1 , select "insert special -> formulas"
  • You should see that merged cell 0
  • Enter Col1 in the merged cell
  • Now you should see that all B2, C2, D2 will be Col1 , that is, now you can refer to the merged cell, as you expect.

If you can use several merged cells, each of which has a different width, just paste the formula for all of them at a time.

The reason for this is because Microsoft offers a design choice for perculier . It looks like when you paste formulas into merged cells, each base cell gets a formula (in contrast, if you enter a value, it gets only the top left cell). Thus, you can take advantage of it and insert the formula that refers to the cell next to it, and then overwrite the upper left cell with the desired value, then each cell that lies at the base of the merged cell will have this value.

+11
source

I created 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

+4
source

I understand that I was late on this topic, but I found a very simple answer to this question.

If, for example, your label is merged through 4 columns a1: d1, and if you refer to b1, you will return "". To dynamically find the correct labels, use this fx in your new table:

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

I am sure you will understand that this will capture ranges in e1: h1 etc. when you drag.

What is it. Hope this helps someone.

+1
source

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

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

0
source

A more general version of the e.James proposal:

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

It depends on the fact that the merged cells are empty, except for the first (if you are not in such a case as Martin's proposal).

Note. In curly brackets there is an array formula mark (do not enter them, just press alt + return to check the formula in the cell).

0
source

More articles:


All Articles