Excel: Concatenate/retrieve cells that are across a merged key cell

Question

Hello,

Assume a simple sheet like so:

-------------
|  1  |  a  |  Need formula to return: "a,b,c"
|     |-----|
|     |  b  |
|     |-----|
|     |  c  |
-------------
|  2  |  a  |  Need formula to return: "a,b"
|     |-----|
|     |  b  |
-------------

The first column is the merged key cells and the second column has an unknown number of rows with text values in each cell.

What I want is a means, such as VLOOKUP/HLOOKUP, to retrieve a list of the cells across the merged key cell.
Even if I want to use Macros, how do I retrieve the a-b-c cell range programmatically as a result of VLOOKUP on the "1"?

If that doesn't work, I'm fine with just a logical check on whether a letter exists in the list.
For example, given a key value, I want to be able to programmatically apply conditionals like:

- "Does 'a' exist at '1'? Yes."
- "Does 'c' exist at '2'? No."

EDIT:
Keep in mind that the above is an example; I do not necessarily know that there is only 'a', 'b', and 'c'; I do not know the number of rows in each set.

Thanks.

Answer 1

This will give you True/False, though you can nest it in an IF statement to get Yes/No.

=NOT(ISERROR(VLOOKUP("b",INDIRECT(ADDRESS(MATCH(1,A:A,0),2)&":"&ADDRESS(MATCH(2,A:A,0)-1,2)),1,0)))

The variables are "b" which is the letter to look for, the 1 in the first match statement which is the number to key off of, and then the 2 in the second match statement is just the 1 variable +1.

The formula first determines the range of cells in column 2 for the 1 variable, by determining where it starts, and then going to one less than where the next number ends. Of course this will only work if the numbers are consecutive and ordered. It then does a VLOOKUP on the range that was determined, which for your example would be $A$1:$A$3.