Functions: INDIRECT

INDIRECT function returns the reference to a cell based on its string representation. It returns the reference specified by a text string. References are immediately evaluated to display their contents. Function syntax is as below.

INDIRECT(ref_text, [A1])

ref_text is a textual representation of a cell reference.

[A1] is optional. It is either a TRUE or FALSE value. TRUE (or if this argument is omitted) indicates that ref_text will be interpreted as an A1-style reference. FALSE indicates that ref_text will be interpreted as an R1C1-style reference. (We will see more on this referencing style in one of the future posts)

Example 1

Let’s see the actual usage of this function. INDIRECT function allows you to input the address of one cell in another and get data from the first cell by referencing the second.  Let’s assume this: cell A1 has a value C3. Cell C3 has a value 10. Now, entering =INDIRECT(A1) in any cell fetches 10. So, cell A1 value which is C3 is evaluated to get its value.

Example 2

Taking it further, assume this situation. I have a file where I compile 12 months data, each month in a different sheet. Each sheet is named by the month; January, February etc. I have a summary sheet that displays key calculations for a particular month. I have a drop-down list in the summary sheet, when I select a month from the drop-down list, few key numbers from that month needs to be populated. For example, one of key number to be populated includes a formula to give sum of respective month's sheet cells F1:F10 i.e. like =SUM(January!F1:F10) for January month.  Is there any way that I can do this without manually writing so many formulas?

As usual, Excel says yes. Excel's INDIRECT function was designed specifically for this sort of thing. This function accepts a text string as an argument, and then evaluates the text string to arrive at a cell or range reference. In your case, assume that cell B1 on your summary worksheet holds the month name. The following formula utilizes the INDIRECT function to create the range reference used by the SUM function:

=SUM(INDIRECT(B1&"!F1:F10"))

Whatever month is selected in B1, this function returns that particular month related data by summing cells F1 to F10 of that month named sheet.

Example 3

You need to sum a series of cells in column A but starting row and ending row numbers are dynamic (changes frequently). If you write a SUM formula, you will have to update it each time your start and end row number changes. You can solve this problem using INDIRECT function. Suppose you entered your starting row in cell B1 and ending row in cell C1.  Then, you can use the formula;

=SUM(INDIRECT("A"&B1&":A"&C1))

If B1 contains 5 and C1 contains 10, this evaluates to the string "A5:A10". INDIRECT function converts this string to an actual range reference, which is passed to the SUM function.

Example 4

In our post relating to relative & absolute references, we saw this example. (Refer to section: Point to remember)

When you create a formula that refers to a cell, the reference to the cell will be updated if: (1) the cell is moved by using the Cut command to delete the cell or (2) the cell is moved because rows or columns are inserted or deleted. If you always want the formula to refer to the same cell regardless of whether the row above the cell is deleted or the cell is moved, use the INDIRECT worksheet function. For example, if you always want to refer to cell A10, use the following syntax:

=INDIRECT("A10")

Example 5

We used this function to get cascading lists in one of our earlier post.

Hope we are clear on using INDIRECT function. Do let me know otherwise. Happy Learning!!

Functions: ROW & COLUMN

Hi, let’s start new month with a post on Excel Lookup & Reference functions. In this post, we will be discussing about Excel built-in functions ROW, COLUMN. I do not use these functions as much as I use other functions in Excel lookup & reference category. Nevertheless, knowing these functions doesn't harm. You never know, sometimes these are just the functions you wish you knew. Okay, let's see about ROW function.

ROW

ROW function returns the row number of a cell reference. Let’s see the function syntax.

ROW( [reference] )

reference is optional. It is a reference to a cell or range of cells. If the reference parameter is omitted, then the ROW function assumes that the reference is the cell address in which the ROW function has been entered in.

ROW function entered in cell A1 without any reference given returns value 1 as cell A1 is in row 1.

Let’s see few examples:

Formula in cell A2 omits reference. Since cell A2 is in second row, formula returns 2 as result.  Formula in cell A3 is referring to cell A5 which is 5^th row hence the result is 5.

ROW function is most useful as an argument in other functions rather than by itself. You can see a more practical example of this function in our previous post where we were trying to get cascading lists with only unused items.

COLUMN

On very similar lines with ROW function, COLUMN function returns the column number of a reference. Function syntax is as below.

COLUMN( [reference] )

reference is optional. It is a reference to a cell or range of cells. If the reference parameter is omitted, then the COLUMN function assumes that the reference is the cell address in which the function has been entered in.

COLUMN function entered in cell B1 without any reference given returns value 2 as cell B1 is in second column.

Few examples:

Formula in cell A2 omits reference. Since cell A2 is in first, formula returns 1 as result.  Formula in cell A3 is referring to cell D5 which is 4^th column from start hence the result is 4.

COLUMN function is most useful as an argument in other functions rather than by itself.

Points to remember:

1. ROW & COLUMN functions returns the row & column number respectively starting from 1^st row and 1^st column of any sheet. That means cell B5 will always have row number 5 and column number 2. These functions won’t work if you are looking for row and column numbers relative to a range. I mean to say, you selected a range say B3:D10 and you want the row number of cell B5 relative to the range selected (i.e. taking B3 as first row, so B5 is 3^rd row in the range).
2. Instead of actual references, you can also provide named ranges as arguments. For example: If you name range A1:A5 as MyList, you can enter ROW(MyList) instead of ROW(A1:A5)
3. If you provide a range that refers to more than one cell, Excel returns ROW / COLUMN number of the first cell in that range.

In the next post, we will see about 2 related functions in the same category. Happy Excelling!!

Functions: ROWS & COLUMNS

We saw about ROW & COLUMN functions in the previous post. In this post, we will see about 2 more functions in Excel lookup & reference category viz. ROWS & COLUMNS functions. This would be very small post.

ROWS

This function returns the number of rows in a reference provided as an argument. Function syntax is as below.

ROWS(array)

array: Unlike ROW function, this argument is mandatory here. This argument can refer to a cell, range of cells, array or named range.

Few examples:

As the function returns the number of rows in the given reference, formula in cell A2 results 1 (cell A1 contains only 1 row) and cell A6 formula result is 9 (there are 9 rows in range D3 to D11).

COLUMNS

On the same lines with ROWS function, COLUMNS returns the number of columns in a reference provided as an argument. Function syntax is as below.

COLUMNS(array)

array: mandatory argument. This argument can refer to a cell, range of cells, array or named range.

Few examples:

As the function returns the number of columns in the given reference, formula in cell A2 results 1 (cell A1 refers to only 1 column) and cell A3 formula result is 2 (there are 2 columns in range B3 to C3).

Functions: LOOKUP

Let's start the discussion about 'Lookup & Reference' functions with LOOKUP function. Simply put LOOKUP function looks for a value either from one row range or one column range or from an array (array is set of rows and columns) and gives a corresponding value from same position in a second row or column. Continue to read below to see this function at length.

Excel LOOKUP function has two syntaxes.

LOOKUP(lookup_value,lookup_vector,[result_vector])

LOOKUP(lookup_value,array) 

First syntax is called 'vector' form and the latter one is 'array' form. 

Vector form

A vector is a one row or one column range. This form of LOOKUP searches for a value in the given row or column and returns corresponding value in another one row or one column range. Function syntax is below.

LOOKUP(lookup_value,lookup_vector,[result_vector])

lookup_value:  It is the value you want this function to search for. This can be a text, number, logical value, a cell address or a name.

lookup_vector: This is the place where this function should search for lookup_value. This range must be spread over one column OR one row only.

result_vector: This is the place where values you wish to retrieve are in. This range must be spread over one column OR one row only. Also should be of the same shape & size as lookup_vector. Its optional. If this is omitted, first column of data is considered.

Note 1: If LOOKUP cannot find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value.

Note 2: If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP gives the #N/A error value.

Let's see this with an example: I have a table with set of numbers & names along with a colour assigned to each value as in below picture.

I tried LOOKUP with various formulas and see the results below.

Array form

Array form of this function is used when your data is in a tabular format and lookup_value is in the left most part of the table. This form searches lookup_value in the first column of given array and returns  corresponding value from the right most column in the same row. Function syntax is below.

LOOKUP(lookup_value,array) 

lookup_value: Same as vector form. What you are looking for.

array: Table form. If rows are more than columns, lookup_value is searched in first column. If more columns in the array then lookup_value will be searched in first row.

Important points to note:

1. Values in lookup_vector & array must be sorted in ascending order. If that's not done, LOOKUP may give incorrect result. If you cannot sort the data, consider using functions like VLOOKUP, HLOOKUP or MATCH.
2. For lookup_value, upper case and lower case text is equivalent. If you are searching for 'a' and lookup_vector contains 'A', result will be corresponding value of 'A'.
3. Result_Vector and lookup_vector must be identical in terms of shape & size.

Advantages of LOOKUP:

1. You can use LOOKUP function as an alternative to multiple IF functions.
2. You can return a value which is left side of lookup_value using vector form. Functions like VLOOKUP does not support this unless you tweak the standard function a bit.
3. This function supports looking values in rows and columns as well. In other functions either row wise or column wise search is possible (VLOOKUP for column search, HLOOKUP for row search)
4. Arguments need not be cell references. You can enter your own set of data in the formula itself. For example: Below function takes lookup_value from cell C2 and lookup in data in first set of curved brackets (i.e. A to I) and returns corresponding value from second set of curved brackets (1 to 9)

=LOOKUP(C2,{"A","B","C","D","E","F","G","H","I"}, {"1","2","3","4","5","6","7","8","9"})

Disadvantages of LOOKUP:

1. Since your data must be sorted first, this function is not flexible.
2. If lookup_value is not available in the list, LOOKUP considers next least number. This is not friendly if you are looking for exact match.

That's it about lookup. Post a comment if you have any doubts relating to this function or have anything to say. Happy learning!!

Functions: VLOOKUP - Left lookup

Hi again!! We saw about VLOOKUP function in last post. We also saw that it works to find a value which is in the first column of table array or towards right from it. So VLOOKUP fails to lookup a value to the left. However there are some cases where you have to apply lookup but have required value to the left side of lookup_value position in table_array.  You do not have permission to change the column sequence or simply do not want to do that. In this case your hope is LOOKUP or a tweaked version of VLOOKUP.

In this post I will show you a way to tweak the standard function and make it work this way. Let's see a quick recap of how VLOOKUP works.

VLOOKUP function looks for a value (lookup_value) in a set of rows and columns (table_array) and returns a value from the same row where lookup_value has been found but from a different column we ask for (col_index_num). You can also request an exact match or an approximate match (range_lookup). Looking closely at table_array description, we can notice that this argument supports the following; 

1. a range reference (like $A$3:$A$17) or 
2. a named range (example: if you have a table named as MyTable, you can simply type MyTable as the second argument instead of range) or
3. a formula that results in the range reference

What we used in our VLOOKUP example in earlier post is the range reference. To lookup a value to the left we will use 3rd property above that tricks VLOOKUP to believe the given array is on the right side where as in reality it will be towards the left. Let's see one example.

I have a list of asset codes along with asset names in a data base. As you see in the below picture, asset code is the first column and next column (to the right) is asset name.

Given the asset code, I can easily use VLOOKUP to retrieve asset name. But if I have asset name given and have to find out asset code which is on the left, VLOOKUP gives up. See the below formula.

Since VLOOKUP always look for lookup_value in the first column and in our example it will not be able to find a match, it results in to an error (#N/A). So what to do now? In this particular example you can use the flexibility of LOOKUP.

But if you want to use VLOOKUP, let’s see how to tweak this function to work in these cases. Enter the below formula and see how it works, below I will explain what we did.

You can cross check the results manually from data base to see results are in fact correct. Once you made sure we are good with the formula, we will now analyse the above function.

We just followed VLOOKUP syntax as required. As we know that VLOOKUP finds a value in the table_array provided and return corresponding value from a chosen column , we have provided table_array that is created by CHOOSE function by combining column B first and then column A. This tricks VLOOKUP to believe column B is indeed first column and column A is second. We will slow down now and analyse what CHOOSE function is doing, bit by bit.

Let’s have a closure look on how we use CHOOSE and what it means. We used the below as an argument in VLOOKUP above.

CHOOSE({1,2},$B$1:$B$11,$A$1:$A$11)

We are giving 2 ranges to CHOOSE function which is B1 to B11 and A1 to A11 (last part of the function) and asking it to combine them. Notice that we are giving column B first, then column A and we asked it to combine first one and second one. VLOOKUP considers the combined range for second argument instead of our initial database.That’s the trick. You may get a doubt now. Can we give column A first, then column B and ask CHOOSE to combine 2^nd one first and 1^st one next? Answer is YES. Below formula also works perfect.

CHOOSE({2,1},$A$1:$A$11, $B$1:$B$11)

Essentially, both of above syntaxes creates an array with Column B and Column A. Try these steps to see it yourself. We use CHOOSE as an array function below.

1. Select a 11 rows * 2 columns range.
2.  Type / paste the function “= CHOOSE({1,2},$B$1:$B$11,$A$1:$A$11)” without quotes
3.  Press Ctrl+Shift+Enter

Now you can see Excel filled up the selected range like below.

Notice that Asset name is in first column and Asset code later. Our original database is other way around. Once the CHOOSE function does this with in the Excel memory, VLOOKUP does the remaining job of finding and returning the value which is actually to the left of the table.

You can add any number of columns like this to CHOOSE function and extend this function to suite your need. For example, if you have 3 columns in your data (A, B and C) and you want to create an array with  an order B, C & A, just use CHOOSE like below.

CHOOSE({2,3,1} ,$A$1:$A$11, $B$1:$B$11, $C$1:$C$11)

That’s it guys! This way you can make VLOOKUP work with the help of CHOOSE function to return left side value. Post a comment if anything seems confusing above. See you next time!

Functions: CHOOSE

As part of Excel ‘Lookup & Reference’ functions, we have already seen few details about LOOKUP & VLOOKUP in previous posts. In this post let’s see one more useful function under this category i.e. CHOOSE.

CHOOSE returns a value from list given as argument based on the position of the value in the list. For example, if you have 10 values in a list and want the value in 8th position, you can use CHOOSE function. Function syntax is below.

=CHOOSE (Index_num, value1, [value2],…..[value254])

Index_num: Is the position you want to return the value from.

• If index_num is 1, CHOOSE returns value1; if it is 2, CHOOSE returns value2; and so on. 
• If index_num is less than 1 or greater than the number of the last value in the list, CHOOSE returns the #VALUE! error. 
• If index_num is a fraction, it is truncated to the lowest integer before being used. 

Value1 to 254 are the list items. Value1 is mandatory and all others are optional. So you can provide upto 254 values to choose from. Note that the limit is 29 if you are using Excel 2003 or earlier versions.

Example: Let’s see a practical example. One of my clients runs a business. He uses 2 rebate tables for his customers. Table 1 is for normal customers. Table 2 is for frequent / premium customers. Both rebates are based on the sales value, but % of rebate is more in Table 2. Here are the tables.

Now when he bills, he has a field to select if the customer should be given rebate under Table 1 or Table 2. What he wants now is when he selects the applicable table number, the rebate % should be auto picked. We already know that we can use VLOOKUP to pull out the rebate but that only works with one array. Since we have 2 tables, we have to use nested formula using IF and VLOOKUP functions. Sales amount is in cell B10. I need rebate % in D10. Formula looks like this.

This works pretty well within the given example. However what if you have multiple numbers of tables and multiple conditions to check with. You have to nest series of IF functions one in another and add that many LOOKUP functions. Your formula becomes gigantic and even you won’t understand it on a second look. An easy way around is CHOOSE function. Formula looks like this.

You just need to enter your table ranges inside CHOOSE function as values and let VLOOKUP pick the correct one based on the table to be applied.

Let’s analyse our function. VLOOKUP looks for sales which is in B10 and as a second argument CHOOSE function will decide which range VLOOKUP to look in. Based on the table number selected in C10, it picks either A2:B7 (first table) or D2:E7 (second table). In our fist example, we selected table number 1 so A2:B7 becomes the second argument for VLOOKUP. It returns 2nd column from the array which is rebate %. As a last argument for VLOOKUP we gave 1 i.e. approximate match.

Like most of the Excel functions, CHOOSE is more powerful when combined with other functions like VLOOKUP, SUM, MATCH etc. CHOOSE function looks less messy if you give names to your tables and use named ranges. We will talk more about named ranges in future posts. Happy learning!!

Functions: VLOOKUP

Hello. I am starting the new year with a post on VLOOKUP function. VLOOKUP is one of the most used Excel 'lookup & reference' functions in my experience. Though many of my friends and colleagues are having difficulty in understanding how to use this function, it's fairly simple once you understand the syntax and dirty your hands with couple of practical examples. Let's dive into VLOOKUP.

Introduction

VLOOKUP searches for a lookup value in first column of a selected array and returns a value in the same row of another column. V stands for vertical and it's fairly understood as this function works on columns. Function syntax is below.

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

lookup_value: Is the value you are trying to search for.

table_array: Is the set of columns where you are searching the lookup_value in and want to retrieve corresponding value. In this array, lookup_value will always be searched in the first column.

col_index_num: Is the column number in table_array starting with first column of the array as 1 and counting towards right. This must always be a number equal to or more than 1. If you do not provide a number or provide a number less than 1 or provide a number which is greater than the number of columns in table_array, VLOOKUP results into an error.

range_lookup: This is optional. Is a logical value that specifies if you want to look for an exact match of lookup_value or an approximate match. Since this is logical value, only options available are either ‘True’ or ‘False’. In Excel language, True = 1 and False = 0. Hence you can use 1 and 0 also for this option. If you want an exact match, type 0 (zero) or false, else if you are fine with an approximate match then leave this option blank or type 1 (one) or True. Approximate match in this case means the next largest value that is less than the lookup_value. Point to note here is that if you want an approximate match, your first column must be sorted in ascending order just like in LOOKUP function.

Points to remember:

1. Make sure that lookup_value and table_array values contain the same formatting to the cells. I mean to say if lookup_value is stored as text and table_array is stored as numbers, though the value is available in table_array, VLOOKUP may give an incorrect result.
2. Also extra spaces or line break characters will not be visible to the naked eye but VLOOKUP sees it. So seemingly same cell values may not be same always.
3. You can use wild characters for lookup value. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.
4. You cant't have lookup_value appearing 2 or more times in the first column of table_array. Only the first instance will be considered by standard VLOOKUP. You will have to tweak the original function to work in these cases.
5. Table_array left most column should contain the data you are looking for.
6. Use absolute references in the functions, this is handy when you drag down the function to other cells.
7. If you use 1 or True or omit the last argument (to get approximate match), you MUST sort first column of Table_array in ascending order.

Example: Let's see an example to clearly understand how to use this function. For simplicity sake, I have my data in one workbook. So I have a price list data base for different products. What I want is that when I create a bill with some products, it's price should be auto picked. Let's see how we do it using VLOOKUP.

Price List:

Bill: I need price for product A to F. Here are the formulas I use under the price column.

Let's analyse the formula. For product A, I used VLOOKUP to lookup value in D3 which is "A". Lookup to be performed in Array A3 till B17 that is the place where my price list is available. I need the corresponding result from column 2 of the selected array which is where the price is entered. And since I need exact match for the product, as a last argument, I entered 0 (zero, remember you can also enter FALSE in place of 0). Excel did good job in finding the price 100. I checked this formula result and was happy. To fill the balance product prices I just dragged down the formula in below cells.

Now notice the formula for product B, second argument array is now A4:B18 instead of A3:B17 and so on for other products. Since my price list is in the same order as bill, Excel will still be able to return the correct result but most of the times your data wont be arranged this way and VLOOKUP returns error. Above formula is called relative reference whereas what you need is an absolute reference formula to keep the table array unchanged when you drag the formula below. To use absolute reference, after entering the VLOOKUP formula in first cell (i.e. product A in our example), edit (press F2) the formula. Select A3:B17 and press F4. Doing this places $ (dollar) symbol in front of each row and column reference in the formula. This tells Excel not to change the reference when the formula dragged down below. Updated formula looks like below. Now drag the formula to other cells and notice for product B and so on.

This is an example if you are looking for an exact match. But sometimes you may not want an exact match but an approximate match. One of such cases is while calculating tax. Let's see how VLOOKUP works in this case.

So I have tax rates in a workbook like below. For simplicity I used current year tax rates.

For an income of 2 lakhs we don't have any tax. For income crossing 5 Lakhs, we have minimum 10% of 3 Lakhs which is 30,000 plus 20% of income more than 5 Lakhs. So in above picture, minimum tax is the tax calculated if the income has crossed the limit on left side.

Once we make this table ready, calculating tax for any income using VLOOKUP is easy. I have income of my client in cell D1. If I just want to find out which tax bracket my client falls in, I use the below formula.

=VLOOKUP(D1,$A$2:$C$6,3,1)

I took a random income 9,38,938 and the formula show a result 20%. Let's see the function syntax. Lookup value is in D1, I have to look to find in A2 to C6 and used absolute references. I want to return value from 3rd column in array which contains the tax rate and last argument is 1 to say I want an approximate match (remember this argument is optional, you can use TRUE or simply leave out this argument in this case). Though my random income is not available in tax table, VLOOKUP was able to retrieve the correct tax rate for me. In fact if I am looking only to retrieve the tax rate, I don't need the middle column in my tax table. Then why did I add that? My intention is to calculate exact tax payable for that income. To do that, my formula should be something like below.

=VLOOKUP(D1,$A$2:$C$6,2,1)+VLOOKUP(D1,$A$2:$C$6,3,1)*(D1-VLOOKUP(D1,$A$2:$C$6,1,1))

That formula is a bit long but it gives tax payable as 1,17,788 which I checked back and found correct. Let's also see what we are doing in the above formula.

First VLOOKUP looks up for my random income number is tax table and retrieves the applicable minimum tax rate which is second column in the table. We have used 1 as last argument so approximate value will be searched. Result is 30,000. Next VLOOKUP looks for applicable tax rate from 3rd column and result is 20%. Last VLOOKUP finds applicable income bracket from first bracket which is 500000. Once all the VLOOKUPs did their job, it's simple mathematics done by operators placed in between the VLOOKUPs. I leave it to you to figure out the rest.

I have tried the same formula with different income figures as in picture below. Income figures are in column F.

That's it. You can look and find thousands of values in a fraction of seconds using VLOOKUP. However this function suffers from drawbacks. One is that you cannot return a value which is in the left side of array (LOOKUP function supports this). In the coming posts we will see how to tweak VLOOKUP function to work in that case and get more out of it. See you!!

Excel Lookup & Reference functions

Hello. Enough of introduction posts. Let's jump to see how to increase number crunching efficiency in Office which is the purpose of this blog. We will start with most used functions in day to day office work which are lookup and reference functions.

I use lookup functions very frequently. These functions are used to locate a value you are looking for in a set of rows and columns and returns it's address or corresponding value in any other column. 

Excel offers below functions under 'Lookup & Reference' category. (In Excel 2003, 2007, 2010)

• ADDRESS
• AREAS
• CHOOSE
• COLUMN
• COLUMNS
• GETPIVOTDATA
• HLOOKUP
• HYPERLINK
• INDEX
• INDIRECT
• LOOKUP
• MATCH
• OFFSET
• ROW
• ROWS
• RTD
• TRANSPOSE
• VLOOKUP

These functions makes life a lot easier for accountants. I use them for tax calculations, merge monthly budget figures to year end report, We will cover these functions over series of posts from now on. Stay tuned.