COLUMNS Function in VLOOKUP function

Friends,

In my last article I discussed about COLUMN function and I've shown you how to use it with VLOOKUPfunction to make VLOOKUP function more dynamic.

But COLUMN function is good for single column matching, what we will do when there are multiple columns to be matched easily ?

In the function COLUMN we give the reference cell's address and we get the number from the first column (i.e., column 'A') and we use this property of COLUMN function in the VLOOKUP function in column index number to get a dynamic VLOOKUP formula.

But sometimes we face situations where we have to put the column index number in VLOOKUP function not from the first column in the Excel sheet but from column other than 'A' column or first column (e.g., from column 'C4' to 'F4'). In this situation we can use COLUMN function but a little tweak is require (which I'll show in other article). Here in this article we will use another function which will serve the same purpose.

Microsoft Excel provides a function, almost same as COLUMN function that can resolve this problem, the function is COLUMNS. Note, it is COLUMNS, an extra 's' is added at the end which suggest that it can be used to count the number of columns more than one from any starting column.

We need to pass an ARRAY into COLUMNS function which will return the number of columns that is included in the array. Please don't be anxious about ARRAY, it is very easy besides if you don't know about ARRAY you can still use this function.

It is also one of the easiest functions in Microsoft Excel. Lets' first understand this function then we will use it in VLOOKUP function to make VLOOKUP function more dynamic.

Description

The Microsoft Excel COLUMNS function returns the number of columns in a cell reference.

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

Syntax

The syntax for the COLUMNS function in Microsoft Excel is:

=COLUMNS(array)

Returns

The COLUMNS function returns a numeric value.

Example of COLUMNS function

Write "=COLUMNS(D2:F2)" in G2 cell and it will return the number of columns from 'D' to 'F' i.e., 3 in this case.

How to use COLUMNS function in Excel

Now we'll use this numeric value in column index number portion of VLOOKUP function.

How use COLUMNS function in VLOOKUP function

Normally VLOOKUP function looks like =VLOOKUP(J2,B1:F10,5,0). Here 5 is the column index number, we'll replace this by COLUMNS(B2:F2). Hence the formula now looks =VLOOKUP(J2,B1:F10,COLUMNS(B2:F2),0)

COLUMNS function in VLOOKUP function

To make VLOOKUP more dynamic we have used COLUMNS function, but what about next cells, if we assume that there are more than one columns to be mapped using VLOOKUP.

Its' very simple, we'll use $ sign to restrict the incremental property of Excel formula and the we'll just drag it in a row.

The formula now becomes  : =VLOOKUP($J2,$B1:$H10,COLUMNS($B2:F2),0)

Here we have restrict the first cell so that it does not get incremented but we have left the second potion to get incremented while dragged.

= COLUMNS($B2:F2)

Please have a careful look at the below image and you will be able to understand it and this will make VLOOKUP more dynamic.

COLUMNS function in VLOOKUP function

Hope you have enjoyed this article.

Thank you for reading...