top of page

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


Sean O'Shea has more than 20 years of experience in the litigation support field with major law firms in New York and San Francisco.   He is an ACEDS Certified eDiscovery Specialist and a Relativity Certified Administrator.

​

The views expressed in this blog are those of the owner and do not reflect the views or opinions of the owner’s employer.

​

If you have a question or comment about this blog, please make a submission using the form to the right. 

Your details were sent successfully!

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

bottom of page