Excel Tip - Copy A VLOOKUP Components Throughout A number of Columns

 It may be actually irritating you probably have arrange your VLOOKUP method in Excel, your method is working as anticipated wanting up the primary column of values after which it's a must to manually alter the method if you wish to copy it throughout a number of columns.

I wish to search for the month-to-month Gross sales of two varieties of Beanie hats in my whole gross sales information. So, I've arrange my VLOOKUP to search for month-to-month volumes of each Beanie hats Beanie_JL and Model Beanie_JP.

The same old VLOOKUP is working completely properly beginning at my first column which returns the worth from January 2015, but when I drag the method proper to proceed with subsequent months from February 2015 onwards then I don't get the specified outcomes.

Even utilizing absolute references in my method, the COLUMN INDEX NUMBER doesn't transfer on once I drag the method.That is the place most customers would manually alter this to get the outcomes they want. Nevertheless don't want to do that as we will enlist the assistance of one other method together with VLOOKUP. Let's use the COLUMN method to assist us out.

The COLUMN perform is admittedly simple. It interprets a column quantity right into a cell deal with. For instance C1 would return Three as C is the third column in our worksheet, so you possibly can in all probability see how we will use this within the VLOOKUP method.

Let's go over the syntax of the VLOOKUP method is

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

LookupValue. The worth you wish to search for. The worth you wish to search for should be within the first column of the vary of cells you specify intable_array

The table_array. That is the vary of cells during which the VLOOKUP will seek for the lookup_value and the return worth.

Col_index_num. That is the column quantity (beginning with 1 for the left-most column of table-array) that accommodates the return worth.

Range_Lookup.

A logical worth that specifies whether or not you need VLOOKUP to seek out an actual match or an approximate match:

• TRUE assumes the primary column within the desk is sorted both numerically or alphabetically, and can then seek for the closest worth. That is the default methodology if you happen to do not specify one.
• FALSE searches for the precise worth within the first column

The primary COLUMN INDEX NUMBER within the VLOOKUP I wish to return is within the SECOND column of our information set, so as a substitute of utilizing 2 because the COLUMN INDEX NUMBER I can substitute it with B1.The method appears like this

=VLOOKUP($L$7,$B$3:$J$25,COLUMN(B1),FALSE)

As soon as I do that the method will mechanically updates as its dragged.