The following is the syntax of the vlookup formula:
I have seen most people using a hardcoded number in the third argument of the formula. However, the downside of this is that if you drag the formula to the right, you will have change the number manually.
This is where a handy formula COLUMN comes into play.
The COLUMN formula returns the number of the column added as the argument in the formula. Therefore, let's say your array table in the VLOOKUP formula is starting from column A, and your column index is D, then you would enter 4 as your argument for column index. However, you can easily use COLUMN(D1) and this will return 4 as well. Furthermore, of you drag it across, it will change to COLUMN(E1) which will return 5.
If your table starts from column B, you can subtract 1 from the COLUMN formula so that it returns the correct column number. However, let's say your table is starting from column AL and your column index is BX, instead of counting manually the column number or what you want to subtract from the COLUMN formula, you can always just use COLUMN(BX)-COLUMN(AL)-1. This will give you the exact column number which you want and return the correct figure.
Hoping this tip will save you a few seconds of your time counting columns and manually changing the column numbers. Have a great Sunday!
