VLOOKUP Hack
- Sean O'Shea
- Apr 26, 2015
- 1 min read
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
Recent Posts
See AllHow is data generated by aiR utilized by Relativity? Relativity has published a white paper addressing its AI security policies assuring...