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?

 

[gsistek]

A Pivot Table should work perfectly for this.

 

[tps49]

Can't use that because of the reporting systems we have in place (I know that's ridiculous)...chance that there's a formula that works?

 

[tps49]

Although I agree that it would help make things easier, but we'd end up having to write code that refreshed the Pivot Table every time someone needed a report (kind of a pain) rather than just changing the reference cell in a formula.

 

[gsistek]

Assumptions:
Building Name is in col A on 'Sheet1'
Three Letter Code is in col B on 'Sheet1'
Output is on a different worksheet - A1 has the code you want results for.

in A3:

{=IFERROR(IF(INDEX(Sheet1!$A:$A,SMALL(IF(Sheet1!$B:$B=$A$1,Sheet1!$B:$B,9E307),ROW()-2)=9E307,"",INDEX(Sheet1!$A:$A,SMALL(IF(Sheet1!$B:$B=$A$1,Sheet1!$B:$B,9E307),ROW()-2)),"")}

This is an array-entered formula. Do not type the brackets in. Use CTRL-SHIFT-ENTER to enter the formula. If Excel adds brackets, you did it right!
Drag the formula down as far as necessary.

Note - Since this references the entire columns, it may be slow to calculate. To help, you can put row references in the formula as well, such as:

{=IFERROR(IF(INDEX(Sheet1!$A$1:$A$1000,SMALL(IF(Sheet1!$B$1:$B$1000=$A$1,Sheet1!$B$1:$B$1000,9E307),ROW()-2)=9E307,"",INDEX(Sheet1!$A$1:$A$1000,SMALL(IF(Sheet1!$B$1:$B$1000=$A$1,Sheet1!$B$1:$B$1000,9E307),ROW()-2)),"")}

 

[tps49]

Gsistek, something still isn't calc'ing quite right. When I remove the brackets and use ctrl shift enter to computer, i get an error message saying that I have too many arguments for the function.

After updating for the layout of my spreadsheet, the formula is:

{=IFERROR(IF(INDEX(Sheet1!$B9:$B103,SMALL(IF(Sheet1!$O9:$O103=$R$11,Sheet1!$O9:$O103,9E307),ROW()-2)=9E307,"",INDEX(Sheet1!$B9:$B103,SMALL(IF(Sheet1!$O9:$O103=$R$11,Sheet1!$O9:$O103,9E307),ROW()-2)),"")))}

Any idea what the problem might be?

 

[Marcelo Branco]

See if this works

Data in Sheet1


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]

Building​

[/td][td]

Designation​

[/td][/tr]


[tr][td]

2​

[/td][td]

Build1​

[/td][td]

ABC​

[/td][/tr]


[tr][td]

3​

[/td][td]

Build2​

[/td][td]

AAB​

[/td][/tr]


[tr][td]

4​

[/td][td]

Build3​

[/td][td]

ABB​

[/td][/tr]


[tr][td]

5​

[/td][td]

Build4​

[/td][td]

AAA​

[/td][/tr]


[tr][td]

6​

[/td][td]

Build5​

[/td][td]

AAB​

[/td][/tr]


[tr][td]

7​

[/td][td]

Build6​

[/td][td]

AAA​

[/td][/tr]


[tr][td]

8​

[/td][td]

Build7​

[/td][td]

ABC​

[/td][/tr]


[tr][td]

9​

[/td][td]

Build8​

[/td][td]

AAA​

[/td][/tr]


[tr][td]

10​

[/td][td]

Build9​

[/td][td]

BBB​

[/td][/tr]


[tr][td]

11​

[/td][td]

Build10​

[/td][td]

AAA​

[/td][/tr]
[/table]

Sheet2 (Report)


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]

Search​

[/td][td]

Count​

[/td][td]

List​

[/td][/tr]


[tr][td]

2​

[/td][td]

AAA​

[/td][td]

4​

[/td][td]

Build4​

[/td][/tr]


[tr][td]

3​

[/td][td] [/td][td] [/td][td]

Build6​

[/td][/tr]


[tr][td]

4​

[/td][td] [/td][td] [/td][td]

Build8​

[/td][/tr]


[tr][td]

5​

[/td][td] [/td][td] [/td][td]

Build10​

[/td][/tr]
[/table]

Put the Designation of interest in A2

Formula in B2
=COUNTIF(Sheet1!B:B,A2)

Array formula in C2 copied down
=IF($B$2>=ROWS(C$2:C2),INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$B$2:$B$500=$A$2,ROW($B$2:$B$500)),ROWS(C$2:C2))),"")

confirmed with Ctrl+Shift+Enter

Hope this helps

M.

 

[tps49]

Marcelo,

A couple of issues with this:

- When I change the Designation of interest, the Countif updates with the correct number but the List output does not update. Oddly enough, when I shift ctrl + shift + enter in this formula, I'm not getting the brackets (and not sure why, since I got brackets for the previously-suggested solution)
- When I try to copy the array formula down, I get an #NUM error

Any thoughts?

 

[gsistek]

My mistake...
Try
{=IFERROR(IF(SMALL(IF(Sheet1!$O9:$O103=$R$11,Sheet1!$O9:$O103,9E307),ROW()-2)=9E307,"",INDEX(Sheet1!$B9:$B103,SMALL(IF(Sheet1!$O9:$O103=$R$11,Sheet1!$O9:$O103,9E307),ROW()-2))),"")}

 

[tps49]

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)