Generically return array between cells with arrayformula?

Question

indirect and offset do not work properly with arrayformula, so for example

a
c
x
s
q
p

So I can use match or vlookup to find out that c is row 2 and q is row 5 but then I want to return an array of {c,x} it's pretty easy to accomplish with offset or indirect but how would you do it when it is an arrayformula (wanting to return multiple combinations) in one cell that can return everything between c and q and also between x and p, etc.

Answer 1

maybe:

=INDIRECT(
 ADDRESS(MATCH(B1, A:A, 0), 1, 4)&":"&
 ADDRESS(MATCH(B2, A:A, 0), 1, 4))

Answer 2

There is a much simpler way to do this using hlookup.

Are you asking if given 2 and 5, can you pull {c,x} in a single array formula?

Answer 3

After understanding the problem a little better, I thought I might offer this sample sheet as a potential option. The single arrayformula is in cell E2. Feel free to make a new tab to re-state the problem if i still don't quite understand it.

Sample Sheet

Cheers, Matt