Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Retrieving Values From Excel Merged Columns

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?

like image 697
cLFlaVA Avatar asked Jan 15 '09 17:01

cLFlaVA


People also ask

How do I get data from merged cells in Excel?

Click Alignment > Merge cells > OK. Click Find All to see a list of all merged cells in your worksheet. When you click an item in the list, Excel selects the merged cell in your worksheet. You can now unmerge the cells.

How do you reference a cell in a merged cell?

Enter the formula (Cell reference 1& " "& Cell reference 2) into the cell where you want the combined values to appear. For example, if you want to combine the values of cells A1 and B1 into cell C1, then enter (A1&" "&B1) into cell C1. Next, copy and paste this in other cells where you want to combine values.

Can you Vlookup a merged cell?

The Vlookup function will work in merged cells as usual in Google Sheets, but the output won't be what you are seeking. What's the solution to it? For example, if an item has three prices based on its grade, we may enter such data as below. Item: Mango (A1:A3 merged and entered it).

How do you split the contents of the merged cell across multiple cells?

To unmerge cells immediately after merging them, press Ctrl + Z. Otherwise do this: Click the merged cell and click Home > Merge & Center. The data in the merged cell moves to the left cell when the cells split.


1 Answers

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.

like image 162
Martin Avatar answered Oct 21 '22 15:10

Martin