Excel: Get the cell address of the first occurrence of a value and use it in a formula

Question

I have the following table:

     A    B    C
 1   1    4    =(Formula) 
 2   1    
 3   3
 4   4
 5   4

I need to get the address of the first occurrence of the value in B1 (4 in this case) from column A (which is A4) and use that address in a formula (located say in C1). The formula should basically do the following:

1. If B1 is not empty, get the first occurrence of the value in B1 (in this example 4) in range A1:A10000 (in this case A4)
2. Use this address (A4) in a formula, to make a range of 20 cells: this_address:this_address+20 (In this example: A4:A24).
3. Do some calculations

All three steps should be performed in one single formula.

Answer 1

While INDEX is the better choice, OFFSET should at least be mentioned.

'A4:A24
=offset(a1, match(b1, a:a, 0)-1, 0, 21, 1)
=sum(offset(a1, match(b1, a:a, 0)-1, 0, 21, 1))

'D4:D24
=offset(a1, match(b1, a:a, 0)-1, 3, 21, 1)
=sum(offset(a1, match(b1, a:a, 0)-1, 3, 21, 1))

Answer 2

Use INDEX/MATCH and INDEX/MATCH+20:

INDEX(A:A,MATCH(B1,A:A,0)):INDEX(A:A,MATCH(B1,A:A,0)+20)

So if we wanted to sum that range:

=SUM(INDEX(A:A,MATCH(B1,A:A,0)):INDEX(A:A,MATCH(B1,A:A,0)+20))