Ever encountered challenges in identifying the required column while using =VLOOKUP() on large, complex tables that stretch into several columns? There is an easy way out.

Most users hard-code the column number in the VLOOKUP formula e.g. =VLOOKUP (“Bat Roger”,$B$4:$Y$83,16,FALSE). However, this number can be replaced with a =COLUMN() formula that will seek out the 16th column. The other benefit of using the =COLUMN() formula is that this formula can be copied to the right without need to manually update the column number in every instance of the formula.

e.g. =VLOOKUP(“Bat Roger”,$B$4:$Y$83,COLUMN($B$4:P$4),FALSE)  ===> Ensure the column range’s end reference is mixed (P$4, not $P$4 or P4).