Using HLookup and or Index and Match

[Peter Davison]

I have a one column (GP3 to GP200) which includes a number of headings and below each heading is the cell contents I want to retrieve (always 4 rows below the heading).
The selection for the heading is on a list in a separate cell (FP6) for the user to select so it needs to find the heading from Cell FP6 in the list (GP3 to GP100) then return the cell contents 4 rows below.
I'm really stuck on this I just can't get the answer right.
Any help would be appreciated.

 

[etaf]

=INDEX(A2:A15,MATCH(D1,A2:A15,0)+5)
probably able to use OFFSET

D1 has value to lookup
E3 has the formula to return a value

you can see i said match with a 5 as the lookup in D5 and then add 5 rows , and return from A2:A100 the 5th value down from the matching value

Book7.xlsx
	A	B	C	D	E
1				5
2	1
3	2				10
4	3
5	4
6	5
7	6
8	7
9	8
10	9
11	10
12	11
13	12
14	13
15	14
Sheet3

Cell Formulas
Range	Formula
E3	E3	=INDEX(A2:A15,MATCH(D1,A2:A15,0)+5)

 

[Peter Davison]

Thank you so much, it works perfect.
It looks so simple, yet I just couldn't get it right.
Appreciate you quick response and help.

 

[etaf]

you are welcome