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?
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.
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.
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).
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.
Here is another solution that can also work when the merged cells are of different widths, let me illustrate with an example:
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With