It can be really frustrating if you have set up your VLOOKUP formula in Excel, your formula is working as expected looking up the first column of values and then you have to manually adjust the formula if you want to copy it across multiple columns.
I want to look up the monthly Sales of two types of Beanie hats in my total sales data. So, I have set up my VLOOKUP to look for monthly volumes of both Beanie hats Beanie_JL and Style Beanie_JP.
The usual VLOOKUP is working perfectly well starting at my first column which returns the value from January 2015, but if I drag the formula right to continue with subsequent months from February 2015 onwards then I do not get the desired results.
Even using absolute references in my formula, the COLUMN INDEX NUMBER does not move on when I drag the formula.This is where most users would manually adjust this to get the results they need. However do not need to do this as we can enlist the help of another formula along with VLOOKUP. Let’s use the COLUMN formula to help us out.
The COLUMN function is really straightforward. It translates a column number into a cell address. For example C1 would return 3 as C is the third column in our worksheet, so you can probably see how we can use this in the VLOOKUP formula.
Let’s go over the syntax of the VLOOKUP formula is
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
LookupValue. The value you want to look up. The value you want to look up must be in the first column of the range of cells you specify intable_array
The table_array. This is the range of cells in which the VLOOKUP will search for the lookup_value and the return value.
Col_index_num. This is the column number (starting with 1 for the left-most column of table-array) that contains the return value.
Range_Lookup.
A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:
- TRUE assumes the first column in the table is sorted either numerically or alphabetically, and will then search for the closest value. This is the default method if you don’t specify one.
- FALSE searches for the exact value in the first column
The first COLUMN INDEX NUMBER in the VLOOKUP I want to return is in the SECOND column of our data set, so instead of using 2 as the COLUMN INDEX NUMBER I can replace it with B1.The formula looks like this
=VLOOKUP($L$7,$B$3:$J$25,COLUMN(B1),FALSE)
Once I do this the formula will automatically updates as its dragged.
