# referencing cells - Retrieving Values From Excel Merged Columns

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

- Open a fresh Excel, merge
*B1, C1, D1* - Type
**Col1**in the merged cell - In
*B2*, type formula**=B1**, and in*C2***=C1**, in*D2***=D1** - You should see
*B2*to be**Col1**while*C2, D2*are**0** - In
*B3*, type the formula**=A3**, copy it - Right-click the merged cell
*B1:D1*, select "paste special -> formulas" - You should see the merged cell being
**0** - Type
**Col1**in the merged cell - 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.

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?