VLOOKUP Hack


Many people are familiar with Excel's VLOOKUP formula which can be used to add data to a spreadsheet when you have a control number or other reference point that appears in another data source. It looks like this:

=VLOOKUP(A2,Sheet2!$A$1:$Z$10000,2,FALSE)

So if you have a value in column A on worksheet 1 [=VLOOKUP(A1], that also appears in a table of different data on worksheet 2, [Sheet2!$A$1:$Z$10000] this formula (in the above example) will search for the exact value in column A of worksheet 2 [,FALSE)] , and then pull the entry in second column [,2].

If you change the 'A1' at the beginning of the formula to '$A1', then when you pull the formula to the right in Excel using CTRL + R, or the fill handle, the reference to the control number will stay the same but the reference to the column in worksheet 2 will not change to get the data from the adjoining column. The formula still gives you a copy of what was in the first column you entered it in. If you want to pull data from a lot of columns on worksheet 2 it can be time consuming to update the formula in each column.

Instead of manually changing the numbers, try this hack:

=VLOOKUP($A$1,Sheet2!$A$1:$Z$1000,COLUMN(B1),FALSE)

Then when you fill the next column with this formula, 'COLUMN(B1)' will automatically change to 'COLUMN(C1)', and you'll get data from the next column on worksheet2


Contact Me With Your Litigation Support Questions:

seankevinoshea@hotmail.com

  • Twitter Long Shadow

© 2015 by Sean O'Shea . Proudly created with Wix.com