1
I have a master worksheet which contains data that I wish to link to another 'summary' sheet using a lookup.
However, some of the cells whose data I wish to include in the summary sheet are merged across two or more adjacent rows. To be clear, the 'primary' column A that I am using in my formula in order to identify the target row does not contain merged cells, but the column from which I wish to return a value does.
I have tried VLOOKUP
and INDEX+MATCH
. The problem is that the data is only returned for the first row's key, and the others return zero (as though the cell in the target column were blank, where actually it is merged).
I have tried inelegant ways around this, e.g. using IF
statements to try to find the top row of the merged cell. However, these don't work well if the order of values in the summary sheet is different from that in the master sheet, as well as being messy.
Can this be done?
2I believe a fuller description of the nature of the lookup would help. Two suggestions. (1) Only the top left cell of a merged area has a value. So if B6:D10 is merged, B7 for example is blank. This is true for VBA so I assume it is true for formulae. (2) Some VBA functions do not "notice" merged areas. For example, FIND will not "see" a merged area. – Tony Dallimore – 2012-01-09T14:17:53.537
This is possible, but I think we need to see the way the sheet is setup. Vlookup will only return results to the right of the lookup column, for instance, but maybe a sumif formula would work. – Raystafarian – 2012-02-04T18:39:04.273