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