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


19 views0 comments

sheet1.png


0 views0 comments

sheet2.png


0 views0 comments

Sean O'Shea has more than 15 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.

 

All content provided on this blog is for informational purposes only. The owner of this blog makes no representations as to the accuracy or completeness of any information on this site or found by following any link on this site. The owner will not be liable for any errors or omissions in this information nor for the availability of this information. The owner will not be liable for any losses, injuries, or damages from the display or use of this information.

 

This policy is subject to change at any time.