Vlookup based on Moving Target range

east3rd

New Member
Joined
Sep 8, 2008
Messages
48
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
As far as I understand what you are trying to acheive then, like.....

Excel 2010
ABCJKLMAA
1DataDataDataDataResult
2Lookup Valuexxx<199A441
3Data Column12<298b432
4Result From AA9<< Find from AA397c423
5496d414
6595e405
7694f396
8793g387
9892h378
10991xxx369
111090j3510
121189k3411
131288l3312
Sheet4
Cell Formulas
RangeFormula
B4=INDEX(AA1:AA200,MATCH($B$2,INDEX(J1:U200,,$B$3-9),0))
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

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
Back
Top