I'm fairly new to using Excel beyond it's basic functions and I am trying to do VLookups for a huge sheet. Problem is I need to drag the formula in both the rows and columns because the data is collected over many years. My spreadsheet is set up somewhat like this. All data/sheet names/etc are arbitrary.
A B C D 1 Ticker Company Jan Feb 2 MCD McDonalds 5 7 3 XYZ XYZ 12 15
C2 would have the formula =VLOOKUP(A2, DATA!$B$1:$Z$300,COLUMN(DATA!C:C),FALSE)
Then drag that forumla down Column C.
I then need to change the lookup_value in C2 to $C$2 so I can drag the formula across from C2 to W2 (so the lookup value doesnt change when I drag). Then repeat this process for C3, C4, C5, etc.
Instead of manually editing, can I record a macro to do this for me? Or, is there a simpler way to do it all together?
Bookmarks