Vlookup with Column function

In this post you not only learn Vlookup you will also learn how to use a column function to avoid manual efforts.
Many people use Vlookup in day to day work, if it’s used in one column, then no issue, but if using it in more than one column, then the changing column index number is manual task which most of people don’t like.

When you copy and paste formula to the next column, then column index number remains the same it never changes like other values (range/cell reference). To make the column index number dynamic we can use “Column” function.

First will see what Vlookup is?

This function looks at the first column of an array and moves across the row to return the value of a cell. In other words, it will find the specified values or relevant information from the array (the input or source table).

Syntax:

=Vlookup (lookup_value, table_array, col_index_num, [range_lookup])

Detailed breakdown of the syntax:

lookup_value: the values which you want to lookup (eg: “Text” or A1).

table_array: the source data table from where you want to lookup (eg: $A$1: $D$5).

col_index_num: Third argument is column index number, number of column in array from the lookup values column.(eg: if looking up data from Column A to get details from Column C then the Index number will be ‘3’).

In this argument we will use a column function to make column index dynamic.

[range_lookup]: even though it’s an optional argument, it plays a very important role to get the correct data, this argument is where we define exact or approx match by selecting true/false or 1/0.
For exact match use 0 and for approx use 1.

How it works:

In below example there are two tables one is working table where we are filling data using vlookup function and the other one is source table from where we are looking up data.

As you see if I write the formula in fist cell and drag it throughout the table then column index number won’t change, it remains the same number “2”. ''=VLOOKUP ($A4, $H$3:$L$12, 2, 0)”

Now will use the column function to make this column index dynamic so you just need to write formula once and drag it throughout the table and you will get correct data.

As given in above table I replaced column index by column function.

What Column function do?

Returns the column number of the given cell reference (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.).

Syntax:

=Column ([Reference])

If you write only column function without any reference it will how that columns index number
eg: write formula in B2 cell “=Column()” the 2 value will appear

Reference: it nothing but a cell reference eg: in B2 cell write “=Column(D1)” formula and now 4 value will appear.

Detail function of this formula given below.

How Column function works: Column function will write index number of column you mention if you look at in above table in cell B3 the formula is “=VLOOKUP($A3,$A$17:$E$26,COLUMN(B1),0)”. In column I took “B1” as reference instead of column index “2”

Column function will show the index number like for A1= 1, B3=2, C6=3….. Z1=26 here row number doesn’t affect only the column reference matters.

To check whether you selected right column reference use “F9” function key select the column function and press “F9” function key “=VLOOKUP ($A3, $A$17:$E$26, COLUMN(B1), 0)” it will show the outcome of that formula as “=VLOOKUP ($A3, $A$17:$E$26, {2}, 0)”

Note: While using Vlookup make sure you are applying correct cell references.