Author: Aniruddh

Using VLOOKUP on Excel-Explained with Syntax and Examples

VLOOKUP functions of excel is very useful when you need to return a value from a table or a range of cells by looking up another value. VLOOKUP on excel can be used for finding the value in a range which are present in the other range in the same sheet or other sheet and other excel files. Let is quickly look into the syntax of vlookup function and examples on how to use VLOOKUP on Excel. Do note than simple VLOOKUP function works with only single criteria. If you are looking for 2 or more criteria vlookup, the please refer my previous post multi criteria vlookup.

Syntax of Excel VLOOKUP Function:

= VLOOKUP( value, table_array, index_number, Typeoflookup )

where

Value: is the value to search for in the first column of the table_array or range of cells. Value can either a text or a numerical value.

table_array: is the range of cells where you need to lookup for the value.

index_number: is the column number in table_array from which the matching value must be returned. A index_number argument of 1 returns the value in the first column in table_array; a index_number of 2 returns the value in the second column in table_array, and so on.

Typeoflookup: This is not mandatory. However this will chose between exact match and relative match. If you use TRUE or if you omit this argument, then non exact match lookup will be performed. If this is set to FALSE, then vlookup does the exact match

Note and Troubleshooting Tips on Excel:

If “Typeoflookup” is either TRUE or omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.
If “Typeoflookup” is FALSE, the values in the first column of table_array do not need to be sorted.
Excel vlookup returns #VALUE! if “index_number” is less than 1 and returns #REF! if the “index_number”Greater than the number of columns in table_array. Also one more error value comes with VLOOKUP is #N/A. Excel returns #N/A when there is no exact match or the value you are matching in the range is not found.
If there are two or more values in the first column of table_array that match the lookup_value, the first value found will be returned

Example on Using VLOOKUP formula in Excel:

Sample data

Using vlookup with Exact match:

Considering above sample data, if you need to lookup for the value “Banana” and fetch the Rating, use the formula given below.

=VLOOKUP(“Banana”,A2:C6,3,FALSE) which returns C.

Using vlookup with non Exact match:

This is useful, when you need relative value. Say for example, your matching value based on the “% of commission” and you want to return the ratings. Here do not that, for using non exact match the first column of lookup range must be placed in ascending sort order.

=VLOOKUP(33%,B2:C6,2,TRUE) which returns C. Here first instance of nearby value is taken by the formula.

Formatting the VLOOKUP on Excel using ISNA and IF functions:

When there is no match found, excel returns #n/a. This is will give problems when you need to use SUM function on the return value of returned value.To overcome this, we need to use ISERROR function. This very useful, if you are creating a template with VLOOKUP function.

Examples:

From the same above sample table, we will match Strawberry. The formula returns #n/a as the match is not found on the table.

=IFERROR(VLOOKUP(“Strawberry”,A2:C6,3,FALSE),”Not Found”) which returns “Not found”.

The IFERROR functions syntax is =IFERROR(Value, value if error) where “value” is the data which needs to checked for error and “value for error” is the value to be returned incase of error.

In above example we use VLOOKUP return data as value and “Not Found” used if the returned value is #N/A.

IFERROR function was introduced in Excel 2007. Hence if you are using earlier version of Excel 2007, then you need to use IF function and ISERROR function of Excel. So the formula is as shown below.

=IF(ISERROR(VLOOKUP(“Strawberry”,A1:C6,3,FALSE)),”Not Found”,VLOOKUP(“Strawberry”,A1:C6,3,FALSE))

Note: Do not use this function unless you have earlier versions of excel as this will lead to calculation time. This is because for every time you use the function, excel needs to calculate vlookup twice. when range to lookup is more and number of instances of using this formula more, then excel will take really long time to calculate and return the right values.
Please do share your feedback and tips which you know in the comment section below. If you need any specific support on Excel, then mention your query in comment section, I will try to answer your query.

Video Tutorial for VLOOKUP

January 9, 2013

Using Excel Multiple condition COUNTIF function

In my previous post, we have seen how to use single criteria Excel COUNTIF function. That works great when you need to count a specific range of cells based on one condition. Some times, there are situation we are required to count range of cells based on 2 or more conditions. So let us have a quick look on the same.

Excel Multi condition count function with AND:

The And criteria count function counts the cells if all the conditions are met. The simple example for this is counting a range of cells which falls in between a numerical value. For example, counting cells that contains a value greater 0 and less than or equal to 20. Any cell that has positive value less than or equal to 20 will calculated in the Excel multi count with AND.For using this we need to use COUNTIF excel function or SUMPRODUCT function.

Syntax of COUNTIFS function:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

Where
criteria_range1: Required. The first range in which to evaluate the associated criteria.
criteria1 : Required. The criteria in the form of a number, expression, cell reference, or text that define which cells will be counted. For example, criteria can be expressed as 32, “>32”, B4, “apples”, or “32”.
criteria_range2, criteria2, … Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.

Note: COUNTIFS works only with Excel 2007 and higher versions. For using multiple condition count in earlier version of Excel we need to sumproduct.

Syntax for using SUMPRODUCT function as multicondition count:

As we have seen earlier, SUMPRODUCT function can be used to count range of cells based on multiple criteria.
Example for using Multicondition count in Excel: Below the sample data.

Counting only the sales of Apple for the month of Jan with value less than 250:
1. Using countifs function: =COUNTIFS(A2:A13,”Jan”,C2:C13,”Apple”, D2:D13,”2. Using the SUMPRODUCT function: =SUMPRODUCT((A2:A13=”Jan”)*(C2:C13=”Apple”)*(D2:D13
250>250>

Using Multiple condition count with OR:

To count cells by using an OR criteria, you should use multiple COUNTIFS functions. For example, counting the number of instances of 8,6 and 5 in the range B2:B13.
=COUNTIF(B2:B13,8)+COUNTIF(B2:B13,6)+COUNTIF(B2:B13,5)

This can be achieved through COUNTIF function array formula of Excel. The below Array formula returns the same result as that of above. As this is a array formula you need to enter the formula using Ctrl+Shift+Enter.
=SUM(COUNTIF(B2:B13,{8,6,5})

Note: You need use flower bracket for 8,6,5 in the above example.

January 7, 2013
Excel COUNTIF function Syntax with Examples

The Excel COUNT and COUNTA formula is useful for the basic counting requirements. However, sometime you need to count a number of cells in a range which meets given criteria. To do this, COUNTIF formula is useful. So COUNTIF formula counts number of cells in a range based on the given criteria.
Syntax of Excel COUNTIF formula:
COUNTIF(range, criteria)
where
range is range of cells which you need to count
criteria is nothing but the condition on which the range should be calculated.

Example for Excel COUNTIF Formula:
Let us take below sample data and use countif function with different criteria.

=COUNTIF(C2:C7,50) :- Counts the range with Value 50
=COUNTIF(C2:C7,E1) : Counts the cells value equal to Range E1
=COUNTIF(C2:C7,”>”&E1) : Counts the cells greater than value specified in the range E1
=COUNTIF(D2:D7,”=COUNTIF(C2:C7,”*”): Counts the cells which contains text
=COUNTIF(A2:A7,”Apple”): Counts only the cells with exact word Apple; not case sensitive
=COUNTIF(A2:A7,”*Apple*”): Counts cells containing text Apple anywhere within the text
=COUNTIF(A2:A7,”*E”) : Counts cells which cell content has the alphabet E in the last
=COUNTIF(B2:B6,”>”&AVERAGE(B2:B6)) : counts the cells which are greater than average of the range.

Using COUNTIF function with named ranges: You can use Named Ranges in Excel countif function. For doing this first we need to define the range. Let us look into the above example and define Range A1:A7 as ” “Data”.

Now we can use the names instead of writing the Ranges. The example for Countif function with named ranges.
=COUNTIF(Data,”Mango”)
Similarly we can use defined names to any range by naming that range. It is useful when we use complicated countif functions.0>

January 1, 2013
Adding Facebook Like box or Facebook Fan box widget to Blogger

Adding a Facebook Fan Box or Facebook Like widget to Blogger helps to get more Facebook fans. So here is a quick tutor to add Blogger widget for Facebook Like Box for the Blogger. Facebook Like Box was earlier called as Facebook Fan Box.
Steps to Create Facebook Like Box Widget for Blogger: The below mentioned steps given are assuming that you already created a Fan Page in Facebook. Hence in this post, I have giving only the steps to create the Blogger Widget for Facebook Like box.
Step 1 : Go to Facebook and open your Facebook Fanpage URL. Copy the address of the fanpage from the URL Bar from the browser as shown below. In my case it is
http://www.facebook.com/ExcelTipsAndTricks

Step 2: Go to the Facebook Social Plugin creator page by clicking Facebook Like Box configuration Page. You will get a page as shown below.

Step 3: Now paste your page URL which you have copied in the step 1, inside the Facebook Page URL text box.
Step 4: Customize the widget size by entering the height and width and color scheme. Keeping the height field blanks takes takes the default size. You can select “Show Faces” Check box to show the images your followers else images will not be shown. You can also enable or disable recent post snapshot in the widget by selecting or un selecting the “Show Stream” Checkbox.
Step 5: Click on Get Code which gives two sets of codes. Select “iFRAME” in the top tab and copy the code.

Step 6: In Blogger, go to “Layout” click on ” Add a Gadget” link. Select HTML/Javascript gadget and paste the code in the content box. Place the gadget in your desired place of the blog.
Step7: Click on “Save Arrangement”. You are done. Your Facebook Like box appears in your blog.

Adding the Facebook Like Box in the blogger helps to gain more Facebook fans and also it helps to improve user engagement on the blog. Let me know if this helps. Share your inputs in the comments section below and hit Like if you like this tip.

December 30, 2012
Surya Namaskara- A complete Yoga for Every one
Surya namaskara means saluting to lord Surya the source of energy to the world. Surya Namaskara is set of yogasana postures. It not only just physical exercise, it is a complete exercise for body, mind and soul. Daily performing Surya Namaskara Yoga postures has many advantages.

Main Advantages of performing Surya Namaskara Daily

Performing Surya Namaskar every day has many health benefits. Some of them are
- Beneficial for the health of digestive system.Regular practice of Surya Namaskara helps to improve the digestive system. It stretches the abdominal muscles and helps to lose excessive belly fat and gives flat stomach.
- Ideal exercise to cope with insomnia.Regular practice of Surya Namaskara calms the mind and helps to get sound sleep. Thus it helps in controlling insomnia and other related diseases
- Practice regulates irregular menstrual cycles. Practicing Surya Namaskar ensures the easy childbirth. It helps to decrease the fear of pregnancy and childbirth.
- Surya Namaskar practice boosts blood circulation and helps to prevent hair graying, hair fall, and dandruff. It also improves the growth of hair making it long.
- Regular practice of Surya Namaskar helps to lose extra calories and reduce fat. It helps to stay thin. Practicing Surya Namaskar is the easiest way to be in shape.
- Sun salutation exercise helps to add glow on your face making facial skin radiant and ageless. It is the natural solution to prevent onset of wrinkles.
- Regular practice of sun salutation boosts endurance power. It gives vitality and strength. It also reduces the feeling of restlessness and anxiety.
- The back muscles are toned up and strengthened to a great extent and the spine and arms become stronger
- It regulates the respiration system and improves lungs functionality
Procedure to perform Surya Namaskara

Surya Namaskara consist of 12 postures. Ideally Surya Namaskara should be done total 13 times along with Surya Namaskara Mantras given below chanting one mantra before starting each cycle of yoga postures.

Surya Namaskara Mantras

1. Aum Mitraya Namah
2. Aum Ravayre Namah
3. Aum Suryaya Namah
4. Aum Bhanave Namah
5. Aum Khagaya Namah
6. Aum Pushne Namah
7. Aum Hiranyagarbhaya Namah
8. Aum Marichaye Namah
9. Aum Adityaya Namah
10. Aum Savitre Namah
11. Aum Arkaya Namah
12. Aum Bhaskaraya Namah
13. Aum Shri Savitra Surya Narayanaya namah

Postures of Surya Namaskara

As said earlier, Surya Namaskara is a set of many Yoga postures. Let us look into different postures of Surya Namaskara.

Step1: Stand straight with your feet together and palms folded at the chest as if in prayer. Your toes can be slightly apart but the heels should be as close as possible.
Step 2:Raise your arms above your head and stretch back as much as possible to bend your spine backwards while inhaling air. Your abdomen should be stretched to the maximum limit and your biceps should be touching your ears with your hands parallel.
Step 3:Bend forward, go down and touch your toes with your fingertips. Don’t bend your knees and try to touch your knee with your forehead. When you do this, your stomach is compressed, so exhale.
Step 4:take your right leg back and stretch it while balancing it on the toe and keep your left leg folded in front of your chest. Keep your palms straight on both sides of the left foot on the ground. Look up and inhale.
Step 5:Take back the left leg as well and keep both the feet together while raising your hip from the ground and balancing yourself on all fours. You will look like an inverted V. Exhale while you do this posture.
Step 6:Slowly come down and rest your palms next to your shoulders, your knees touching the ground but your waist and hip slightly raised above so that they don’t touch the ground. Your face should also be downward facing and hold your breath during this.
Step 7:Lower your waist and raise your torso, making your arms straight and balancing yourself. Feel your spine bend and the abdomen stretch. Inhale when you do this and look straight.
Now, again raise your hip and balance yourself on all fours. Looking like an inverted V. Exhale while you do this.
Step 8:Bring the left leg forward and fold it so that the knee reaches your chin and look up. Keep the right leg behind as before and stretch. Inhale when you do this.
Step 9: With your palm at your toes, raise yourself up so that your forehead touches the knees and your spine bends forward. Exhale in this posture.
Step 10:Get up very slowly and take your arms above your head and stretch backwards while inhaling.
Step 11:Then come back to the first position with your arms folded and relax while breathing normal.
Step 12:Repeat the series with the other leg.
December 17, 2012