Vlookup to pull FROM a merged cell

Hello,

Every vlookup/merged cell post I see assumes the lookup value is the merged cell. In this case, the column index that I am pulling from is the merged cell, and that is causing the following problem:

Cells A1 and A2 are two separate cells, but both are associated with merged cell B1:B2. However, when running a vlookup to find results for cell A2, it pulls back a "0" because it it associated with a merged cell.

Let's say cell A1 is "Scrappy" and cell A2 is "Scooby" and then both are next to a merged cell B1:B2, which is "Doo". When running a vlookup for Scrappy and Scooby, the value next to "Scrappy" will pull back "Doo", but the value for "Scooby" will pull back a "0", even though both are next to the same merged cell.

Can someone explain how to get "Doo" to populate next to "Scooby" instead of a "0" for the associated merged cell?

Thank you!

We are trying to avoid that. The column has a large amount of data with said merged cells, so un-merging them all just to duplicate the data would be counter-productive in terms of time.

Hi
See if helps you. Try this approach

Formula:

Please Login or Register  to view this content.

Only works if merged cells have regularity.

When you say "regularity", do you mean the values of all merged cells need to be the same? Because that is not the case here. I tried your formula, and it worked for the specific example I had, but not for the larger column issue.

Originally Posted by Melvosh

If your merged cell groupings are variable (e.g. 2 cells, 4 cells, 3 cells, 2 cells) finding a single formula to account for that will be very difficult. Unfortunately, there's nothing in Excel that detects whether a cell is merged, except VBA, so there's no way in an Excel formula to say "go to the first part of this merged range".

If unmerging and duplicating the data would be a one-time thing, and you're interested, there's a way to do it fairly easily. WARNING: Try this out on a copy of your data before doing it on your actual data!

1. Select the column with the merged cells by clicking on the column header
2. Click the down arrow next to "Merge & Center" and select Unmerge Cells
3. Hit ctrl+g to open the Go To window, and click Special
4. Select "Blanks" and click OK. All of your empty rows should be selected.
5. Type = and hit the up arrow, then hold ctrl and hit enter
6. All of your merged cells should now be unmerged and filled with the correct data

Thank you! This is probably the best case scenario if there is no actual formula to run it while merged.

Hi

Try this for a non regular merged cells.
A formula to sequential use

Formula:

Please Login or Register  to view this content.

A formula to a single value in E4

Formula:

Please Login or Register  to view this content.

See the file for clarification