Using MATCH within OFFSET without a defined range size

[tps49]

Hi there,

I'm working on a spreadsheet that has two columns:

- The first column is the name of a particular building (i.e. 132 Wacker Drive, formatted as text)

- The second column is some designation of random letters that denotes specific qualities about the building. These letters are in groups of three and can only be A, B, or C, and can repeat (i.e. AAA is a group, AAB is a group, and so on and so forth).

What I'd like to do is use a reference cell where I can put in the name of a group (like AAA or AAB) and have it return a list of the buildings that have that group designation. My issue is that not all of the group designations have the same amount of buildings (i.e. AAA might have three buildings, AAB might have seven, etc.).

I'm thinking that the solution has to be a match function nested in an offset, but I can't think of how to make the defined range within the match dynamic...any thoughts?

 

[tps49]

And Gsistek, still not working...I'm just getting blanks returned.

 

[tps49]

And Gsistek, still not working...it's just returning blank cells when I calculate the array.

 

[gsistek]

And Gsistek, still not working...it's just returning blank cells when I calculate the array.

Ahhh
Change both occurrences of ROW()-2 to ROWS($A$1:A1)

 

[Marcelo Branco]

Marcelo, I've now got an array working but it just returns the first property name in however many spaces the array has (regardless of the count for the set, so, it'll give a name back 11 times even if the set has 4, 6, 8, or however many members)

Could you provide a small data sample where my formula doesn't work?

It worked perfectly for me.

BTW to confirm an array formula correctly
select the cell
paste (or type) the formula
with the cursor inside the formula bar in the middle of the formula:
hold down both Ctrl and Shift keys
hit Enter

check if Excel wrapped the formula with curly-braces {=....}

M.