Using HLookup and or Index and Match

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
223
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,866
Office Version
  1. 365
Platform
  1. MacOS
=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
ABCDE
15
21
3210
43
54
65
76
87
98
109
1110
1211
1312
1413
1514
Sheet3
Cell Formulas
RangeFormula
E3E3=INDEX(A2:A15,MATCH(D1,A2:A15,0)+5)
 
Solution

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
223
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 24, 2012
Messages
4,866
Office Version
  1. 365
Platform
  1. MacOS
you are welcome
 

Forum statistics

Threads
1,141,060
Messages
5,704,038
Members
421,323
Latest member
Exidous

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top