Vlookup based on Moving Target range

[east3rd]

Hi everyone,
Pulling my hair out today trying to figure this one out! I have a large reference doc where the given value I'm trying to locate can be found anywhere between columns J - U. From there I need to lookup the value in column AA, hence my vlookup range is somewhat dynamic. I am already using the following formula to locate the column number, for example:

=COLUMN(INDEX('[PPV_CRTC 2018-2019.xlsx]Movies'!J$1:U$1,SUMPRODUCT(MAX(('[PPV_CRTC 2018-2019.xlsx]Movies'!$J$2:$U$520=A2)*(COLUMN('[PPV_CRTC 2018-2019.xlsx]Movies'!$J$2:$U$520))))-COLUMN('[PPV_CRTC 2018-2019.xlsx]Movies'!$J:$J)+1))

The above formula returns a value (12) which equates to column "L" (for example). Given I can locate the "starting" point, how do I now lookup the value in column AA? As always, your help is appreciated.
Thx

 

[Snakehips]

Can you not then use INDEX(AA) MATCH("L") ?

 

[east3rd]

Thanks for the quick response - could you expand on your suggestions? Perhaps my last formula lead you to believe I know more about Excel then I do ; )

Can you not then use INDEX(AA) MATCH("L") ?

 

[Snakehips]

As far as I understand what you are trying to acheive then, like.....

Excel 2010
	A	B	C	J	K	L	M	AA
1				Data	Data	Data	Data	Result
2	Lookup Value	xxx	<	1	99	A	44	1
3	Data Column	12	<	2	98	b	43	2
4	Result From AA	9	<< Find from AA	3	97	c	42	3
5				4	96	d	41	4
6				5	95	e	40	5
7				6	94	f	39	6
8				7	93	g	38	7
9				8	92	h	37	8
10				9	91	xxx	36	9
11				10	90	j	35	10
12				11	89	k	34	11
13				12	88	l	33	12
Sheet4

Cell Formulas
Range	Formula
B4	=INDEX(AA1:AA200,MATCH($B$2,INDEX(J1:U200,,$B$3-9),0))

 

[east3rd]

Perfect!
Thank you so much