Column Function in VLOOKUP Function

Friends,

today I am going to discuss about  a new function in Microsoft Excel, if used with another function like VLOOKUP will prove to be a very useful function and it will make your formula very dynamic.

The function is COLUMN, as the name of this function suggest it gives us the information about column. 

In this article I'll show you how to use this function with VLOOKUP and will make our basic VLOOKUP function very dynamic and will shorten the time to write multiple VLOOKUPs.

Let us first know about this function. Its' very easy and basic function to remember and it comes under LOOKUP functions in Excel.

Description

The Microsoft Excel COLUMN function returns the column number of a cell reference.

The COLUMN function is a built-in function in Excel that is categorized as a Lookup/Reference Function.

Syntax

The syntax for the COLUMN function in Microsoft Excel is:

=COLUMN( [reference] )

Parameters or Arguments

reference

It is optional. It is a reference to a cell or range of cells. If the reference parameter is omitted, it assumes that the reference is the cell address in which the COLUMN function has been entered in.

Output or Returns

The COLUMN function returns a numeric value like 2,3,4 etc.

Example of Column Function

Write "=COLUMN()" in any cell and it will return the column number of that particular column where the function is written if there is a blank within the bracket.

Column Function without Reference

Its' very easy... Now let me write another example ...

Write "=COLUMN()" in any cell and select or type any cell reference within the bracket e.g., "=COLUMN(D3)".

It will also return the column number of that particular column. The benefit is you can get the column number of the referred cell.

Column Function with Reference

Now we will use this property of COLUMN function in VLOOKUP. You remember in VLOOKUP functions I wrote the syntax as VLOOKUP( value, table array, col_index, [range_lookup] ).

col_index is the column number, so if instead of writing column number I write column and then the put the reference of that column within this function then the result will be same. For eample the formula =VLOOKUP($U2,$B:$C,COLUMN(B2),0)

(Here $ sign is to fridge the cell, as you know).

VLOOKUP function in one Cell using COLUMN function

What is the benefit we get using this function ?

or

How to use VLOOKUP function with COLUMN function ?

To understand it lets' take an example, suppose if we have four consecutive columns that we have to fill with data collected from VLOOKUP then we have to write the column numbers four times even if we have dragged the VLOOKUP formula to the consecutive cells. But we can reduce our time by dragging one VLOOKUP formula instead of writing column numbers in each and every VLOOKUP formula if we use COLUMN function within VLOOKUP function with reference cell within the column function, an example of Nested function. But do not forget to increase the range of table array otherwise it will display #REF! error, e.g.,

 from    =VLOOKUP($U2,$B:$C,COLUMN(B2),0)

to       =VLOOKUP($U2,$B:$G,COLUMN(B2),0)

The below Image will make it more clear.

VLOOKUP function in multiple Cells using COLUMN function

Hope you like this article, thank you for reading...