ROW Function in HLOOKUP function

In my last article, I discussed about the function COLUMN & COLUMNS and the use of these functions with VLOOKUP, nested.

In this article, I will show you another function ROW which is also is a useful function in Microsoft Excel if used wisely.

ROW() works same as the COLUMN function, the only difference is it gives you the number of the row where this function is written.

Let us first describe the function and then we will see how can we use this function in Excel.

Description

The Microsoft Excel ROW function returns the row number of a cell reference.

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

Syntax

The syntax for the ROW function in Microsoft Excel is as follow.

=ROW( [reference] )

Parameters or Arguments

Reference

Optional. It is a reference to a cell or range of cells.

Returns

The ROW function returns a numeric value.

ROW Function without Reference

It’s a simple function to remember and to use. The very basic question which may arise in your mind is where to use it. I use ROW function to make HLOOKUP dynamic. Why HLOOKUP ?

Because HLOOKUP is such a function which looks up for data row wise, and like VLOOKUP where it asks for COLUMN number, HLOOKUP asks for ROW number. We must enter the ROW number to get the desired data using HLOOKUP.

The trick is to use ROW function in HLOOKUP’s row_index_number argument or parameter.

Lets take one example of it.

Suppose we have a table arranged in the following way. We should get the data row wise using HLOOKUP. The syntax of the HLOOKUP function is as follows.

=HLOOKUP(lookup_value, table-array, row_index_number, [range_lookup])

Row function in Microsoft Excel

Now instead of writing the row number in row_index_number, if we write ROW() and if we show this function the reference, it will not be necessary to add the row number manually. Because the row number now will be supplied by the ROW function itself.

Please note that while entering the function ROW within HLOOKUP function you have to give the cell's address in the ROW function.

=HLOOKUP(E15,E2:H5,ROW(E2),0)

Please refer to the below image. The working of ROW function is same as the working of COLUMN function.

ROW function in HLOOKUP function

Hope you have enjoyed this article.

Thanks for reading... 

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

Pivot Table Calculated Field - Step by Step

Friends,

In the last two articles we have learned about how to create or insert a PivotTable and few Customization in Microsoft Excel. In this article we will discuss about Calculated Fields in Pivot Table.

But before that let us first know ...

Why should we use Calculated Fields and in which circumstances we should use this technique.

Let me ask you a question ?

Have you ever try to sum two columns in a Pivot Table ?

Let me clear it, if you want to sum first two columns using SUM function or using plus sign like sales value of product 1 & product 2 in the last blank column what will happen ?

You will find a long formula starting GETPIVOTDATA and six parameters (!!!), total value will be calculated for one cell. Okay, now if you drag it to sum the entire data range (that we normally do), you will get zeros all the cells except the first cell. This is because when you try to sum two cells from Pivot Table by default Excel uses the GETPIVOTDATA function with static cell ranges. This is the reason not to get the sum in all cells.

Now in this situation we have one option left i.e., sum by reference (i.e., mentioning that 'sum C2 cell with D2 cell' for example), but here is also we might have problems because in future if you want to change the Pivot Table structure there will be either a blank column between the Pivot Table and the newly created column outside the Pivot Table or Pivot Table will ask you to overlap the column which you just created.

But think if we have some technique to sum these two columns within the Pivot Table as individual column then these problems will vanish and additionally you will get an opportunity to add the new column whenever you need in the Pivot Table.

Yes... there is a process to do this in Microsoft Excel PivotTable, which is known as Calculated Field.

Calculated because you put your own formula to calculate from the Pivot Table column(s).

Field because your newly created column will be listed in Pivot Table as a new Column or Field.

How to Create Calculated Field in Pivot Table

       1.   Select any cell within Pivot Table, you will see two additional Tabs Options and Design

       2.   Click on Option Tab

       3.   Under Tools submenu, click Formulas beside PivotChart

       4.   Click Calculated Field

Calculated Field Options Tab Then Formulas

       5.   A new small window will appear, "Insert Calculated Field"

a)  In the first input box labeled as Name type a name for your Field, for example, 'Sum Of Three Products'

b)  Go to next input box either by clicking in the box or you can hit TAB button

c)   Here you will put formula to be calculated, e.g., I need sum of three columns so I typed SUM after equal sign and selected the fields separated by commas, you can use plus sign without Typing SUM.

d)  To select the fields to be added you need to select it, and either you can double click or can click on the Insert Field Button, notice a single opening and closing quote is automatically printed in the formula box for each item.

e)   Click OK and you are done, you can now see a new field in the Pivot Table and you can also find it within the PivotTable Field List Box.

Insert Calculated Field Window

Create Calculated Field Window - Using Formula

New Field Added as a Column within Pivot Table

New Field Added as a Column within Pivot Table

This is how to create Calculated Field in Pivot Table. Now I will show you...

How to Use Logical Function IF in Calculated Field in Pivot Table

To use logical IF function within Pivot Table as Calculated Field the steps are same as mentioned earlier, remember if you use IF function in Calculated Field it only displays the numbers after calculation, no Texts are allowed, if you try to evaluate Text based upon the result of IF it will give you #VALUE error.

Please watch carefully the following picture you will understand how to use IF in Calculated Fields.

Using IF function in Pivot Table Calculated Field

Using IF function in Pivot Table Calculated Field

Remove or Delete a Calculated Field from PivotTable

It is very simple.

From Options tab click Formulas to open the Insert Calculated Field window.

From Name drop down list select the Field you want to Delete, click Delete button below Modify button, click OK and done.

Remove or Delete a Calculated Field from PivotTable

Modify a Calculated Field from PivotTable

If you need to rectify formula or the field name, its' better to do it through Modify button.

From Options tab click Formulas to open the Insert Calculated Field window.

From Name drop down list select the Field you want to Modify. Make necessary changes in the Name or Formula and then click Modify button and click OK and you are done.

Modify a Calculated Field from PivotTable

Calculated Field in Pivot Table

Few Things About Calculated Fields in PivotTable

Limitations

       1.   Calculated field formulas cannot refer to the pivot table totals or subtotals

       2.   Calculated field formulas cannot refer to worksheet cells by address or by name.

       3.   Sum is the only function available for a calculated field.

       4.   Calculated fields are not available in an OLAP-based pivot table. (OLAP - Online Analytical Processing, we'll discuss in another article).

Features

       1.   For calculated fields, the individual amounts in the other fields are summed, and then the calculation is performed on the total amount.

       2.   Calculated fields are automatically available in all pivot tables that are based on the same pivot cache

That's all friends for today. Hope the topic discussed today is easily understandable. Keep reading & keep asking & commenting...

Thank you ...