It can be really frustrating if you have set up your VLOOKUP formula in Excel, your formula is working as expected looking for 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 find the monthly sales of two types of beanie hats in my total sales data. So I configured my VLOOKUP to search for monthly Beanie hats Beanie_JL and Style Beanie_JP volumes.
The usual VLOOKUP works perfectly fine starting from my first column that returns the value for January 2015, but if I drag the formula to the right to continue with the following months from February 2015 onwards, I don’t get the desired results.
Even using absolute references in my formula, the COLUMN INDEX NUMBER doesn’t move when I drag the formula. However, you don’t need to do this, as we can request the help of another formula along with VLOOKUP. Let’s use the COLUMN formula to help us.
The COLUMN function is really simple. Translates a column number into a cell address. For example, C1 would return 3 since C is the third column in our worksheet, so you can probably see how we can use this in the VLOOKUP formula.
Let’s review the syntax of the formula VLOOKUP en
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
Search value. The value you want to find. The value you want to find must be in the first column of the cell range that you specify intable matrix
The table_array. This is the range of cells that VLOOKUP will search for the search value and the return value.
Col_index_num. This is the column number (starting with 1 for the leftmost column of table matrix) that contains the return value.
Search range.
A logical value that specifies whether you want VLOOKUP To find an exact or approximate match:
- TRUE it assumes the first column in the table is sorted numerically or alphabetically, and then it will look for the closest value. This is the default method if you don’t specify one.
- FALSE look for the exact value in the first column
The first COLUMN INDEX NUMBER in VLOOKUP that I want to return is in the SECOND column of our dataset, so instead of using 2 as COLUMN INDEX NUMBER, I can replace it with B1.
= VLOOKUP ($ L $ 7, $ B $ 3: $ J $ 25, COLUMN (B1), FALSE)
Once you do this, the formula will automatically update as you drag.