Home / Lookup Functions
Showing posts with label Lookup Functions. Show all posts
Showing posts with label Lookup Functions. Show all posts
Subscribe to: Posts ( Atom )
Showing posts with label Lookup Functions. Show all posts
Showing posts with label Lookup Functions. Show all posts

Saturday, September 19, 2009

Function Indirect()

Indirect()
This function is used to return the reference to text reference provided. This function converts the text reference to actual reference and fetches the value available in the address referenced. (It will be much clear when we will take a look at an example)




Syntax:


= Indirect (Text_reference, Reference_format)


Text_reference: This is the text form of cell address or reference from where the value is to be fetched


Reference_format: Reference format can either be TRUE or False. True indicates that text reference is in A1 style and False indicates text reference is in R1C1 format




Examples:


Let us take an example here. In cell A1 write, B1. And, in cell B1 put any value (for example, 10). Now in cell A5 write this function:


= A1


This function will return 'B1' as a result as we have referred to the cell A1's value. 


There could be another case - if we want to get the value of cell which is mentioned in A1. In such case we make use of Indirect() function. This function will the reference (the address of the cell from which we need to fetch the value) from the cell specified in the function. Now, change the function in cell A5 to this:


= Indirect (A1)


This function will now return the value stored in the cell B1 (the cell which is mentioned in the cell A1)


These functions are used mostly in the case where the address or the cell to be referred to are dynamically created. 

Read more...
Function Indirect()SocialTwist Tell-a-Friend
Posted by Dhakkanz at 7:58 PM , 1 comments
Labels: ,

Wednesday, September 16, 2009

Function Index()

Index()


Index function can be used in 2 forms - Array form and the Reference form. I will take each of these here.


Array form: In the first type, it is used to fetch a value from a table / array by making an intersection point using the row number and the column number. In this form, you can specify only a single array from which the values are to be fetched.


Reference form: This form also works similar to the first one, with the only exception being that in this case, you can mention more than 1 arrays / tables from which the value will be looked up and in the function you also mention which reference (i.e. the table / array) should actually the should be fetched from.




Syntax:


Array form: = Index (Array, Row_number, Column_Number)


Array: This argument is the array from which the value should be picked. The array can be a table or a range of cells with single column or row
Row_number: This argument specifies the row number from which the value will be picked
Column_number: This argument completes the function by providing the column number which will be used to create the intersection with row number to fetch the number




Reference form: = Index (Reference, Row_number, Column_number, Reference_number)


Reference: This argument specifies the ranges / tables from where the the value should be fetched using the index number. In case there are more than 1 references mentioned, this argument itself should be enclosed within the parentheses

Row_number: This argument specifies the row number from which the value will be picked
Column_number: This argument completes the function by providing the column number which will be used to create the intersection with row number to fetch the number
Reference_number: This argument informs the function which reference should be considered among multiple references provided 


Examples:


Array form: 

Let us create an example worksheet. In the column A fill cells A1 to A10 with the number from 10 to 100 with an increment of 10. And for the cells B1 to B10, fill them with alphabets A till J.

Now let us suppose you want to fetch the item that is in the 2nd  column and the 3rd row of the table that we just created. For this, the formula should be:

= Index (A1:B10, 3, 2) 

The result will be: C. Similarly if we want to fetch the item in 4th row of the column 2, the formula will be

= Index (A1:B10, 4, 2)


Reference form:

Now let us take another example here. In the same that we just created let us create another table. From the cells A15 to A20 fill the range with values 1 to 6. And in the cells B15 to B20 fill the range with alphabets U to Z. Now let us use one of the previous examples. Let us assume that we want to fetch the item in 3rd row and the 2nd column. The modified formula would be:

= Index ( (A1:B10, A15:B20), 3, 2, ....)

In the above function there are 2 things that I will explain here. One - I have mentioned 2 arrays in this function, from either of which I can pick up the value as referred by the row and the column number. 

Second - In place of reference number, I have used three dots (...) instead of using any value. I have kept it like that only for explanation, else it will give an error. This argument will tell the function from which array out of the 2 provided to the function, the value should be picked up from. The 2 arrays are - (A1:B10, A15:B20).

If the reference number is left blank, the function be default takes the first array and returns the value. Otherwise the results would be as follows;
  • = Index ( (A1:B10, A15:B20), 3, 2, 1) – the result would be C
  • = Index ( (A1:B10, A15:B20), 3, 2, 2) – the result would be W
So, the reference_number argument is used to identify which table should be used to pick the value from.

Read more...
Function Index()SocialTwist Tell-a-Friend
Posted by Dhakkanz at 1:13 AM , 0 comments
Labels: ,

Sunday, September 13, 2009

Series on important functions - Lookup Funtions

From today onwards I will be starting a new series of Functions under the Lookup Functions. These functions will deal with functions such as VLookUp, HLookUp, etc. and also the functions which are generally used with these functions. 

These functions are the most used functions where there are huge tables and there is a need of extracting data from these tables.

So keep an eye open from today as I will be discussing all of these important functions here.

Read more...
Series on important functions - Lookup FuntionsSocialTwist Tell-a-Friend
Posted by Dhakkanz at 9:43 PM , 1 comments
Labels: ,

Sunday, September 6, 2009

Function Rows


Rows()

This function is used to determine the number of rows in a given reference (the range of cells referred to in the function). 


Syntax:

= Rows (Reference_range)

Reference_range: This is the range of cells for which the number of rows is to be determined


Examples: 

The function simply returns the number of rows within the range which is passed on as reference with the function. For example:
  • Rows($A$3:$A$3) - result will be: 1 {as there is only 1 row 'A' within the given range}
  • Rows($A$35:$A$38) - result will be: 4
  • Rows($A$3:$A$5) - result will be: 3
  • Rows($A$3:$F$8) - result will be: 6

Application:

Many a times, you have to write a range of formulas (a similar formula over a range of adjacent columns or rows), which should have a number which is dynamic and changes as the row changes. For example - you have to write a formula in cell A5 and the next formula goes in A6, A7, and so on. But in this range of formula, you want one of the parameter to increment by itself. In such a case you can use Rows() function for the dynamic part.

Here is the way the Rows() function would be used in the above example :

= Original formula (Parameter1, Rows($A$5:A5))

Now, when the above formula will be copied to next cell, i.e. A6, it will change to:

= Original formula (Parameter1, Rows($A$5:A6))

Note that $A$5:A5 changes to $A$5:A6 and this will result in 2 which will be passed on as a parameter to the original formula}

Read more...
Function RowsSocialTwist Tell-a-Friend
Posted by Dhakkanz at 2:18 AM , 0 comments
Labels:

What is an alternative to multiple-nested IF-Then-Else functions?

IF-Then-Else - one of the functions that Excel has taken from the programming languages. And it is one of those things that one learns as basics of programming.
If-Then-Else (or simply IF function) is used to make a choice based on condition(s) provided by the user. For example:
In one of the cells, I want to show "True" IF cell A1 contains 1, otherwise it should show "False".
To achieve the above, one can write a simple formula in Excel, i.e. - =If (A1=1,"TRUE","FALSE")
Great! Now let us change the above task a bit, something as - the cell B1 should show the following text based on the number input:


  • If cell A1 = 1, then the cell should read "One"
  • If cell A1 = 2, then the cell should read "Two"
  • If cell A1 = 3, then the cell should read "Three"
  • If cell A1 = 4, then the cell should read "Four"
  • Otherwise should say - "Above 4"
In this case, one can write a multiple IF formula as this:
= IF (A1=1,"One", IF(A1=2,"Two", IF(A1=3,"Three", IF(A1=4,"Four","Above 4") ) ) )
Cool! It works. Now let us make things more complex. Lets keep 7 conditions instead of 4. You can still write the IF function on above lines and it will work fine.
But what if there are more than 7 conditions? And what about the processing time?
Excel does provide you with a useful tool such as IF function, but it comes with a limitation. The limitation is that a cell in Excel can contain only 7 nested IF functions, not more than that.
The other issue with using multiple IF functions is that it makes processing a bit slow, as there are 7 IF functions to deal with. You will not be able to notice the speed if you have only one cell with multiple IF functions, but in case you had a sheet with many such cells, the calculations will take time.
The alternative to IF functions is to use Choose function. This functions allows you to provide several options within the function, which will be processed on the basis of number provided to it as input. I have provided more text on this function in a separate post.

Read more...
What is an alternative to multiple-nested IF-Then-Else functions?SocialTwist Tell-a-Friend
Posted by Dhakkanz at 2:00 AM , 0 comments
Labels: , ,

Tuesday, August 4, 2009

Function Columns


Columns()
This function is used to determine the number of columns in a given reference (the range of cells referred to in the function).


Syntax:
= Columns (Reference_range)
Reference_range: This is the range of cells for which the number of columns is to be determined

Examples:

The function simply returns the number of columns within the range which is passed on as reference with the function. For example:
  • Columns($A$3:$A$3) - result will be: 1 {as there is only 1 column 'A' within the given range}
  • Columns($A$35:$D$35) - result will be: 4
  • Columns($A$3:$A$5) - result will be: 1
  • Columns($A$3:$F$8) - result will be: 6
Application:
Many a times, you have to write a range of formulas (a similar formula over a range of adjacent columns), which should have a number which is dynamic and changes as the column changes. For example - you have to write a formula in cell A5 and the next formula goes in B5, C5, and so on. But in this range of formula, you want one of the parameter to increment by itself. In such a case you can use Columns() function. Let us say that

Here is the way the Columns() function would be used in the above example :
= Original formula (Parameter1, Columns($A$5:A5))
Now, when the above formula will be copied to next cell, i.e. B5, it will change to:
= Original formula (Parameter1, Columns($A$5:B5))
Note that $A$5:A5 changes to $A$5:B5 and this will result in 2 which will be passed on as a parameter to the original formula}

Read more...
Function ColumnsSocialTwist Tell-a-Friend
Posted by Dhakkanz at 4:35 PM , 0 comments
Labels:

Sunday, July 26, 2009

Function Choose()

Choose()

This function is used to perform an action from a defined list of actions, based on the index number provided to the function.


Syntax:

= Choose (Index_number, Option1, Option2, ..., OptionX)

Index_number: This is the numeric value that is passed on to the function to choose from the list of actions provided to the function
Option1,2...X: List of actions / outputs / results from which one should be choosen a result based on the Index number


Example:

Let us take an example where the function in cell 'B1' is written as -

= Choose (A1,"One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten")

Now, let us apply validation to cell A1, so that this cell should accept only values between 1 and 10. Learn here about applying data validation.

This is just to avoid any errors in cell B1 where Choose() function is used, as we have defined only from 1 to 10.

Now you can input any value in cell A1 and you would see the results in cell B1. The cell B1 will give text name of the number entered in cell A1. Such as:
  • If cell A1 = 1 then the cell B1 will show - One
  • If cell A1 = 3 then the cell B1 will show - Three
  • If cell A1 = 7 then the cell B1 will show - Seven
  • If cell A1 = 5 then the cell B1 will show - Fice
Here is a snapshot of the same example (i have used cell C1 and D1 to optimize the space taken by the screen shot):



Application:

This function is generally used where the limitations of If() function creep in. As we know that If() cannot be used more than 7 times in a cell. Now, if really need to have more than 7 options, then choose is the best option available.

It can also be used to hard-code a list of options within a cell. For example, a worksheet has a drop-down menu from which a selection can be made. The drop-downs, as you would know, gives only the index of option selected and not the selection as such. In such cases, this function can be used to manipulate the index number provided to fetch the text of the option selected.

Read more...
Function Choose()SocialTwist Tell-a-Friend
Posted by Dhakkanz at 11:08 AM , 0 comments
Labels:

Thursday, July 23, 2009

Function Row()

Row()

This function is used to determine the row number of a given reference (the cell referred to in the function).


Syntax:

= Row (Reference_cell)

Reference_cell: This is the cell for which the row number is to be determined


Examples:

The function simply returns the row number for the cell which is passed on as reference with the function. For example:

  • Row($A$3) - result will be: 3
  • Row($C$35) - result will be: 35
  • Row($AA$45) - result will be: 45
  • Row($BB$125) - result will be: 125
You can also use this function to determine the row in which the function itself lies. For example, If this function is written in cell $F$5, then the function should return this cell's row number. For this the function can be simply written without the Reference_cell. It will be:

=Row()

...and the function will return the row number of the cell in which it is contained.


Application:

Though this function is very simple to use, but is a very useful functions. This functions is mostly applied with other advanced lookup functions, such as vlookup(), hlookup(), address(), etc. This functions works similar to Column() function.

Read more...
Function Row()SocialTwist Tell-a-Friend
Posted by Dhakkanz at 12:08 AM , 0 comments
Labels:

Wednesday, July 22, 2009

Function Column()

Column()

This function is used to determine the column number of a given reference (the cell referred to in the function).


Syntax:

= Column (Reference_cell)
Reference_cell: This is the cell for which the column number is to be determined


Examples:

The function simply returns the column number for the cell which is passed on as reference with the function. For example:
  • Column($A$3) - result will be: 1 {as column A is the first column}
  • Column($C$35) - result will be: 3
  • Column($AA$3) - result will be: 27
  • Column($BB$3) - result will be: 54
You can also use this function to determine the column in which the function itself lies. For example, If this function is written in cell $F$5, then the function should return this cell's column number. For this the function can be simply written without the Reference_cell. It will be:

=Column()

...and the function will return the column number of the cell in which it is contained.


Application:

Though this function is very simple to use, but is a very useful functions. This functions is mostly applied with other advanced lookup functions, such as vlookup(), hlookup(), address(), etc. Take a look at this query which I received from one of the readers of the Excel Matic. I have made use of this function. (Note that the example uses a few functions which are not yet described on Excel Matic, but will soon be).

Read more...
Function Column()SocialTwist Tell-a-Friend
Posted by Dhakkanz at 11:37 PM , 0 comments
Labels:

Monday, July 20, 2009

Function Address()

Address()


This function is used to custom build a address range in Excel. It converts a text into actual address based on given parameters. The function can return address for a single cell and a range of cell as well.




Syntax:


= Address (Row_num, Column_num, Reference_type, Address_type, Sheet_name)


Row_num: This value determines the row number that the address should refer to
Column_num: This value determines the column number that should be referred by the address
Reference_type: This single-digit parameter determines which type of reference should the address have. The reference codes are - 1=absolute reference, 2=absolute row/relative column reference, 3=relative row/absolute column reference and 4=relative reference. The default reference is 'Absolute' reference
Address_type: This value determines the style of address that should be returned. There is no effect of this parameter on its functioning. The address type codes are - 1 or TRUE = A1 type reference, 0 or FALSE = R1C1 style reference. In case this parameter is not available, the function adopts the A1 style reference, even if R1C1 is the default style in the sheet
Sheet_name: As the name suggests, this parameter provides the sheet name which should be included in the address as its part. This is used when the address should refer to a range which is external to the given sheet


Note: In the above syntax, Reference type, Address Type and Sheet name are optional parameters. Any of these can be mentioned in the function independent of each other




Examples:


Here are a few examples of using Address() function
  • Address(1,2) - result will be: $B$1
  • Address(2,1) - result will be: $A$2
  • Address(2,1,1) - result will be: $A$2
  • Address(2,1,2) - result will be: A$2 (note the reference style, the reference to column 'B' is not absolute)
  • Address(2,1,4) - result will be: A2
  • Address(2,1,1,1) - result will be: $A$2
  • Address(2,1,1,TRUE) - result will be: $A$2
  • Address(2,1,1,0) - result will be: R2C1
  • Address(2,1,1,FALSE) - result will be: R2C1
  • Address(2,1,1,1,Sheet1) - result will be: Sheet!$A$2 (note that the address now contains sheet name also)
  • Address(2,1,1,1,Sheet_new) - result will be: Sheet_new!$A$2
  • Address(2,1,,1) - result will be: $A$2 (note that the third parameter has been ignore, but its place holder remains there as a blank indicated by 2 commas)

Application:

The address function is generally used where a cell range is used, but the range is dynamic in itself. Mostly, it used with the Indirect() function. The Indirect() function is used to convert the text address into actual reference.

For example, in a worksheet, a column should populate values which are dependent on a certain condition. Now this requires the range of cells to be dynamic in the way that it should dynamically change based on the condition mentioned. Here is an example of the application of Address() function with Indirect() function.

Read more...
Function Address()SocialTwist Tell-a-Friend
Posted by Dhakkanz at 1:25 AM , 22 comments
Labels:

Sunday, July 19, 2009

Lookup Functions - New list of functions released

Today I am starting to write about Lookup functions, that include VLookUp(), HLookUp(), InDirect(), etc. Lookup functions are one of the most useful and in-demand functions.


These functions are used in lot of applications, their demand in today's market is very high, one of which is financial industry. And not just financial research, these functions are very famous in other industries also, at all those places where there is high use of Excel spreadsheets. So, knowledge of these functions and their application will provide you with an edge to clear the interviews and also will make you feel confident in using these functions.


So, I will be writing on one function in every 2 days, so that you guys can get time to catch-up with each function and understand it properly. Also, starting from these functions, going forward I will also talk about the application of each of these functions, together with examples. This will provide you with some live examples of using each of these functions. In case you would like to submit any cases which you think could be suitable examples for these functions, do let me know. I will put them up as sample applications.


To start with, today I have written about Address() function and have also mentioned its application with its explanation.

Read more...
Lookup Functions - New list of functions releasedSocialTwist Tell-a-Friend
Posted by Dhakkanz at 1:46 AM , 0 comments
Labels:
Subscribe to: Posts (Atom)