VLOOKUP to fetch 2nd, 3rd or 4th Value

We all use VLOOKUP and must have observed a shortcoming that it only provides you with the first matching result. I mean if we have data where the lookup_value is coming twice or more times, VLOOKUP will give us the result for the first occurrence only. In the data below if your boss is interested in knowing the commission paid to "Mavericks Reality" for their second booking, how will you find that?

Solution will go like this:

You'll be a adding a helper column right next to broker's names and try to get a unique name. I am using COUNTIF function to do the trick for me like: =COUNTIF($B$2:B2,B2)&B2.

What this will do is, convert our lookup_values as 1Mavericks Reality, 2Mavericks Reality and so on (like in the image below). Now you have a unique lookup_value and you can simply use VLOOKUP("2Mavericks Reality",table_array,col_index) and you are done with your answer.

This is simple right?

You can download the sample file to get a better understanding of this tip. It tells you how you create formulae to get 2nd or 3rd occurrence. VLOOKUP FOR 2nd, 3rd OCCURRENCE.

Share this tip with everyone. "Happy Learning"

Filter Asterisk Character ("*")

Hello friends I hope you are keeping track of our Excel tips and learning new things everyday. Now it's time to get ready for another Excel tips. Today's post will be short and but very unique. Today we are going to learn, how to filter the asterisk character ("*"). Suppose you have a data which contains few asterisk character ("*") and you want to filter rows containing asterisk character ("*").

Now if you have been new to this situation you will simply say it's very easy. But mind it, It's not! There is a special technique to this and I'll take you step to step through it.

1. Apply filter to your complete data which contains asterisk ("*")

2. Click on filter drop down, Select Text Filters and than Contains...

3. It will open Custom AutoFilter pop-up box, here against the drop down with contains is another blank drop down. In this use Tilde ("~") before Asterisk ("*") and hit OK.

With this I wrap today's post, practise it and share it with everyone! "Happy Learning"

AND Function: Test Mathematical Conditions!

Moving ahead with our series of logical function, lets evaluate the AND function AND function provides result in TRUE or FALSE as output based on one of more condition. AND function evaluates at least one mathematical expression located in another cell in the spreadsheet.

The syntax it uses is as follow:

AND(logical1,logical2, ...)

Logical1, logical2, ...   are 1 to 255 conditions you want to test that can be either TRUE or FALSE.

Here is an example along-with image, it will help you understand the function in a better way.

I have few number in cell C1, C2 and C3 and I want to test certain mathematical conditions in cell D1 based on these numbers.

I have used the formula as follows

D1: =AND(C1>200,C2>200,C3>200)

If all three cells (C1,C2, and C3) has number greater than 200, the result in cell D1 will be TRUE. If any of these cells have value less than or equal to 200, the output for the AND function will be FALSE. "Happy Learning"

Explaining Array Formulas in Excel

There is special class of formulas available in Excel know as Array formulas or CSE b’coz they are entered by hitting three keys at a time Ctrl+Shift+Enter. There is no doubt this is one of the most powerful feature provided by Microsoft Excel, but this is one of least used feature as well. Array formulas empower you to calculate that you can’t perform with standard Excel function.  

The "Help" in Excel defines them as below:

"An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments."

What motivated me to write on this post is also an interesting story. I have few very brilliant excel users as colleagues, one day one of them told me that he is not aware about the usage of array function. I tried to explain my bit, but he was not convinced with my elucidation.  That made me feels very strange! And decide to dedicate my next post to Array formulas.

I will not go into the details of this function and its application but will surely make you aware how it functions! So let visualize the data given below: 

and if I want you to know the sum of sales, provided more than 6 units are sold for Event product.

The function will go like this G13: = SUM((D2:D11="Event")*(E2:E11>6)*(G2:G11)) and hit Ctrl+Shift+Enter you will get the solution.

So array functions are helpful when we have more than one decisive factor for our result.

Important Tip! If your formula is not giving you the result you want and you believe you have done everything correctly, you can display the calculation in any part of your formula. To do so, just highlight the condition supplied and hit F9. 

It will give you result how Excel is seeing your condition in TRUE or FALSE.  It will give TRUE when the condition applies and FALSE where it fails.

This is a handy tip to remember for troubleshooting the parts of any formula that are giving you problems.

Before concluding this post I would like you to know the number one rule with array formula, use them only when needed and know when & where to use them. I am not trying to de-motivate you for using array formulas but too many arrays will slow down the functionality of your workbook.

And at last I would request to give your feedback for me and share posts with your contacts and social group.  Let’s continue enriching this mutual relation and help grow each other. “Happy Learning”

Two ways LOOKUP formula to extract values from left & right (INDEX & MATCH FUNCTIONS)

I am sure if you’re using Excel at work, then you must have used lookup function (LOOKUP, VLOOKUP & HLOOKUP). There is no doubt that these are most useful & beautiful functions provided in the excel package. But if you haven’t used these function yet, let me take the honor of telling you that a lookup function return a value from a given table by looking up another value in the same table.

Because of our habit of entering data in very common style, VLOOKUP is among the most used function of Excel. But there is a limitation that it cannot go left and with all the LOOKUP functions is that they only look in the left column and return a from a cell to the right. So what I mean by this is LOOKUP function in Excel are useful for one-way lookups, there is function (inbuilt) for two-way lookup.

Today after reading this post you will be able to conquer this limitation, I am sure of that! First I would like you to look at the data given below and visualize it. It a table representing sales figures for ten of my employees in USA. I have named this table as “SalesData”.

Now if you want to know whose region is Minnesota, no lookup function will be useful. This is because, LOOKUP functions only return values to the right and it doesn’t return values from the left side. One transitory solution can be cut the data from column C and insert it in column and use VLOOKUP, but such quick fix are not always possible. Especially when you are not supposed to manipulate the arrangement of data provided.

In this case we will take help of two functions available in Excel namely INDEX & MATCH.

INDEX(reference,row_num,column_num,area_num): This function returns the value of an element in a table or an array, selected by the row and column number indexes. It means this function will provide you result from your data when you supply the row number and column number of the desired result.  The answer will be intersection of supplied row & column number.

MATCH(lookup_value,lookup_array,match_type): This function returns the relative position of an item in an array that matches a specified value in a specified order. It means this function will provide you the number where your lookup value falls in the series.

Now let’s come back to our problem where we have to know the name of the sales person responsible Minnesota region.

The solution will go like this F13:  =INDEX(SalesData[Name],MATCH(D3,SalesData[Region],0)) this will provide the name as “JAMES”

Similarly if you want to know who over achieved his target by highest margin? Solution will be F14: =INDEX(SalesData[Name],MATCH(MAX(SalesData[Difference]),SalesData[Difference],0)) and the name will be “EMMA”.

Now before wrapping the post here I have some homework for you. Please try to find the following using function mentioned above.

• Who has the maximum & minimum target in my team and what were their respective regions? For minimum use MIN function.
• What is the name of person managing Kansas region and how long is he/she with the organization?
• Who has the lowest target among the team?
• Who is the newest member of the team?

Let me know you solutions, you can post them in the comment section!

I hope you like this post and will share this with your friends and colleagues to help us grow the community of excel learners, there are various options you can use to share with people at the end of the post.

Now you also follow me on Twitter, just click on the vertically aligned Twitter radio button to the left of your computer screen to follow!

You can post your feedback as a comment below and I will take a note of it for future. “Happy Learning”

Please download the file from here!

Create a Table in Excel 2007

With the launch of Office 2007, Microsoft has introduced a rich concept to Excel know as "Table". Table in Excel 2007 help you to manage and analyze your data. This feature has less functionality in terms of calculation but it is very useful in storing lots of information consistently. Formatting, sorting and filtering are very easy with Table. Typically, an Excel table has only column headings and no row headings.
 

I would recommend you to organize your data before creating a Table. Few points which you keep in mind are as follows:

• Your data should have a proper and unique heading in column
• There should be no blank row or column in your data
• Your data should be separated with any other information by at least one row and one column

How to create a Table?

Creating a table is very easy in excel.

1. Select the cells that contain data

2. On the Ribbon, click the Insert tab and in the Tables group, click the Table command

3.Click OK and you are done with a default style table

Special functionality of a Table 

• Autofilter and Sort: When you create a Table it provides you with integrated Autofilter and Sort functionality to your data. You don't have to add this feature manually, only this that is required to make it more helpful is your data should have headings in each column.
• Header names while scrolling: If your data is large and it doesn't fit your screen while scrolling new Excel has feature that temporarily replace column letters with the table's column names.
• Automatic expansion of data: When you add new data in row or column of your table, Excel expands the range of your table and takes it as part of your table.
• Automatic formatting: When you add or delete any row into the table it automatically formats the change. You don't have to make edits to the changed data Excel Table will do it for you! 

I hope you find my posts helpful and easy to learn at the same time. Please do share the tips with your friends, colleagues and if you have any feedback for me I will look forward to it. 

"Happy Learning"

Excel Function: IF ( logical_test, value_if_true, value_if_false )

Now in coming two weeks I have decided to discuss functions available in the stable of Microsoft Excel.  I have taken Logical function to start with, as I found them most useful will working data (this is my personal view :) though). To start with I am gonna explain IF function with an example.

The IF function is one of Excel's logical functions and it tests to see if a certain condition in a spreadsheet is true or false.

The syntax for the IF function is:

=IF (logical_test, value_if_true, value_if_false)

logical_test    - a value or expression that is tested to see if it is true or false.
value_if_true  - the value that is displayed if logical_test is true.
value_if_false - the value that is displayed if logical_test is false.

Some of the conditional operator you need to know:
<   - Less Than
>= - Greater than Or Equal To
<= - Less than Or Equal To
<> - Not Equal To

The following two images will tell you how we can use IF function. In cell A1 have value 15 and I want to populate test "Good" or "Bad" based on this value. For example if value is greater than 10 then it is "Good" else "Bad".

 

Formula which I have entered in cell A2: =IF(A1>10,"Good","Bad")

Similarly you can see cell B2 is giving me "Bad" because A2 is less than 10.

You can use this many ways and under numerous conditions. Few days back one of my teacher friend came to me and we were discussing how we can use Excel to help him in work. He gave me a situation where based on a number he wants to assign grades to his student. The parameters to grades were as follows:

A If the student scores 85 or above
B If the student scores 65 to 84
C If the student scores 50 to 64
D If the student scores 33 to 49
FAIL If the student scores below 33

An example of this data looks like this:
To solve his problem I used IF Function. I created a similar table below his data and wrote a formula as B12: =IF(B2>=85, "A", IF(B2>=65, "B", IF(B2>=50, "C", IF(B2 >=33, "D", "Fail" ) ) ) )

I draged the formaula cross the data sheet and got my result.

I hope this is clear to everyone. I know many of you are already using this function.  Kindly let us know how do you use this function in your daily life.  You can post your experiences and example in the comment box, it will help us in learning new things.  "Happy Learning"

Pass this to your friends and colleagues this may also help them! 

To download the example file click here

Create charts directly in a range

This post will be talking about how to create non-graphic chart without using the default chart templates in Excel.  This method use combination of functions to display bars in the call corresponding to the data.

In the screen-shot above, I have data containing targeted (column B) and actual (column C) number of calls made by agents  in an assessment period. Column D represents, the variance of their performance (in %). 

Functions I have used to create this are IF, REPT, FLOOR & ABS.

To re-create this chart in Excel, please have your data handy or enter the data shown in columns A through D, and then write the following formulas:

E2: =IF(D2<0,REPT("g ",FLOOR(ABS(D2*100),1)),"")

F2: =A2

G2: =IF(D2>0,REPT("g ",FLOOR(ABS(D2*100),1)),"")

I have used webdings font to cells E2 an G2, but you can play here with your imagination (can insert as well). Copy the formulas and paste till the end. Right-align the text in column E, and Left-align in column G.

To make the chart presentable specially when your data contain large variances, you will have to adjust the scaling.  That can be achieved by dividing your result of FLOOR function by any number that suits your presentation.

For example in your function;

E2: =IF(D2<0,REPT("g ",FLOOR(ABS(D2*100),1)),""),  "FLOOR(ABS(D2*100),1)" is giving you a high value such as 50 to make it presentable you can adjust the function by diving the result by 5.  So the new function will be IF(D2<0,REPT("g ",FLOOR(ABS(D2*100),1)/5),"") you can change this divisor to any number you want.

I hope you learnt something interesting with the post. "Happy Learning"