Offset Index Match

[Cooki]

I need to get the column next to my target value

here is my data sheet Sheet1

Oct			Nov			Dec
Points available	Points Achieved	% Achieved	Points available	Points Achieved	% Achieved	Points available	Points Achieved
0​	0​		#VALUE!​			#VALUE!​
0​	0​
0​	0​
0​	0​
42​	40​		60​			126​

Sheet2 has my formulas on

01/10/2020​	42​
01/11/2012​	60​

To get the daya for Oct and Nov, im using =HLOOKUP(TEXT(B3,"MMM"),Sheet1!$AD$3:$AL$9,7,0)
which returns Oct and Nov points available

Now i need to get the Points achieved, i have done it with this =OFFSET(INDEX(Sheet1!$AD$3:$AL$9,MATCH(TEXT(B3,"MMM"),Sheet1!AD3:AD9)*7,1),0,1)

But when i drag it down it does not work as AD3:AD9 needs to actually be AG3:AG9, but i cant figure it out.

Hope that makes sense.

Could anyone help please

 

[jasonb75]

With adjacent columns the easiest way would be to use INDEX and MATCH, making the column references relative in the INDEX range and absolute in MATCH.

Book1
	B	C	D
3	01/10/2020	42	40
4	01/11/2012	60	0
Sheet2

Cell Formulas
Range	Formula
C3:D4	C3	=INDEX(Sheet1!AD$9:AL$9,MATCH(TEXT($B3,"MMM"),Sheet1!$AD$3:$AL$3,0))

 

[Cooki]

With adjacent columns the easiest way would be to use INDEX and MATCH, making the column references relative in the INDEX range and absolute in MATCH.

Book1
	B	C	D
3	01/10/2020	42	40
4	01/11/2012	60	0
Sheet2

Cell Formulas
Range	Formula
C3:D4	C3	=INDEX(Sheet1!AD$9:AL$9,MATCH(TEXT($B3,"MMM"),Sheet1!$AD$3:$AL$3,0))

I was so close to solving it ahhhhh lol

very much appreciated works perfect