I’m stumped. Is there a way to extract data from a list without using
the filter feature, and without using macros? I want to do this
strictly by using formulas. For example, in the sheet below, I would
like a formula in cell A16 that looks at the data in cells A2:B12 and
pulls in the first occurrence of a name in the East region, then a
formula in A17 that pulls in the second occurrence of a name in the
East region, and so on. When all is done, cells A16:C18, each with
their own formula, would appear as shown below. NOTE: I’d also prefer
not to juxtapose the “Name” and “Region” columns.

I’ve played around with VLOOKUP, LOOKUP, INDEX, MATCH, INDEX/MATCH
combo, but I can’t seem to get the second and third occurrences.

Using the following formula in cell A16 yields “Sal”, as expected, but
what can I use for A17 and A18?
A16: =INDEX(A3:A12,MATCH(A15,B3:B12,0))

I need to do this because the “Regions” in col. B are calculated
elsewhere and automatically change frequently. I want cells A16:C18 to
update automatically via formulas so the user doesn’t have to bother
with filters. Although a simple VBA macro would be the easy way to do
this (“FOR EACH cell . . .”), unfortunately macros are forbidden in
our sheets.

Thanks for any help you can offer.


A B C
1 <DATA
2 Name Region
3 Ron Central
4 Bill West
5 Sal East
6 Chuck Central
7 Jim
8 Dick West
9 Joe East
10 Allan Central
11 David West
12 Jim East
13
14 <GROUP LISTING
15 East Central West
16 Sal Ron Bill
17 Joe Chuck Dick
18 Jim Allan David