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...

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...

Pivot Table Calculated Field vs Calculated Item

Friends,

In my last articles I have discussed about How to create or insert Pivot Tables, Customizing Pivot Tables and Calculated Field in Pivot Table.

Now I hope you have noticed that there is another option in Formula tab under Option or Analyze menu as Calculated Item. Today I am going to discuss with you about Calculated Item and the difference between Calculated Field and Calculated Item.

Let take an example, in the below Pivot Table I need to add Saturday and Sunday as one day and then this value should come combined with the existing data, i.e., data related to Saturday and Sunday will come as it was, additionally a new item will appear as Sat+Sun with their total value. Here we can use Calculated Item option.

Its' very simple to use, you need to know where & how to use it otherwise you will find Calculated Item as Grayed colored inactive option.

Where to Use Calculated Item ?

Calculated Item is used in situations where we need another Item in the Row Label item field, i.e., as another item in the same column. The below picture will clear it.

How to Use Calculated Item ?

Place your cursor in any cell within Pivot Table but remember the column should be in Row Labels box, i.e., select any cell in the column where you want to add the new Item.

Calculated Item

Now go to Options click Formulas, from the drop down menu select or click Calculated Item.

A new window will appear as Insert Calculated Item in "***". Give a name to the new Item (in my case it is Sat+Sun). From the Fields section click on the Column Name and you will notice the Items are listed in the right side under Items head. In the formula box enter your formula (in my example it is =Sunday+Saturday). Click OK and you are done, you can now see a new Item in the Pivot Table.

Insert Calculated Item Input Box

That's it, the main difference between Calculated Field and Calculated Item is the first option will create an entire Field and the Calculated Item will create a Item in the Pivot Table.

Pivot Table Before entering Calculated Item

Pivot Table After entering Calculated Item

Hope you have understand the difference between the Calculated Field and Calculated Item and now you can easily use Calculated Field and Calculated Item.

Thanks for reading...