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

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.