Excel Help: April 2013

Vlookup is one the most important and most widely used function amongst the available lookup functions in Excel. This is the first complicated formula that I have learnt after I started working.

Purpose:
It is used for searching corresponding value of a given text from a database . For e.g. Searching name, address, telephone number from large customer database for few customer id. There are other alternative lookup formulas like match, offset, Index etc are available. But of all, vlookup is simplest and most widely used function.

There can be two main purposes for which we can use this function.

To append columns/fields in your existing data for additional information. For e.g. You have list of customers with id, names etc and you want to contact them using phone number which is available in another excel data file. You can add column for phone number in your data and use Vlookup formula to get the details for entire list
For searching details like name, address etc for given text. E.g. Employee id, customer id etc.

How to do Vlookup:

Lets take example of case 1 above. In this case, Customer phone number is taken using using vlookup function based on the "cust id" field from "Cust Basedata" worksheet. Lets call worksheet/table where you want the data as Destination (the worksheet/table where you need to insert vlookup formula) and the worksheet/table from where this information is available ("Cust Basedata" worksheet) as Source.

Vlookup function requires 4 parameters (separated by comma sign)...Each parameter is explained below. After explanation of each parameter, formula for first row of our example (i.e. cell C2 of destination) for respective parameter is mentioned in square bracket in blue font.

Lookup_value: This the text for which you want to search corresponding value from another Excel database. In our example Customer ID in destination table is the Lookup_value. [A2]
Table_array (range): It is the range in which your base data is present. In the example, range A1 to C31 of source is the table_array for vlookup. Note that your table_array should always start from the column in which the lookup value is present. i.e. the first column or the left most column of the table_array should contain the lookup value. In our example Column A is the left most column of the table_array and it contains the customer ids which we are searching for. ['Cust Base Data'!$A$1:$C$31]
Col_index_num: This is the column number in the table_array from which the matching value is returned; starting from the left most column in the table_array. So, Col_index number 1 will return value from the 1st column, col_index_number 2 will return value from 2nd column and so on. In our example, column number for phone number field is 3, which we are trying to get in source. [3]
Range_lookup (Lookup type): It's an Optional parameter, a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match.

True or 1 (default if not specified): For approximate match. If exact match is not found, then it will return value for next largest value which is lower then the search value. Personally I feel, this option has very limited use as most of the time you would want to do vlookup for exact match only.
False or 0: Use this option to for searching exact match. vlookup will return #N/A error if it doesn't find the search value in table array. In our example we will use this option, since we are looking for phone number of customer id matching exactly. [False or 0]

Hence the formula for cell C2 will be =VLOOKUP(A2,'Cust Base Data'!$A$1:$C$31,3,FALSE).
After applying drag/copy the same formula in the remaining list. i.e. cells C3 to C11 in destination table.

Important points to remember while using vlookup:

Vlookup function will return #N/A error if the Range_lookup (4th parameter) is False and if it doesn't find the exact match of search value.
It will return #Ref! error when column_index (3rd parameter) supplied is greater than the number of columns in table_array.
It will return #Value! error if column_index is less than 1.
It will return 0 if it finds the match and the corresponding cell for that searched value is blank.
If the source has more than one values then vlookup function returns first value from the top most row. Hence it becomes very important to sort your source data when there are multiple records for the same value to ensure that the relevant rows comes above the other rows. For e.g. in our example, if there are more than one phone numbers for few customers with additional field of last updated date, then data can be sorted based on descending order of last updated date to get the latest updated phone numbers.
Ensure that range you supply in Table_array parameter is absolute cell reference. (done by using $ sign before the column and row number)
Vlookup can be done only from left to right and not vice-varsa

Purpose:
How many time in our daily professional life we come to a situation where we want to further summarize the data/reports given by a pivot table? For e.g. Grouping all south locations in sales pivot, grouping designations in headcount summary etc

Steps:
One easy method of doing this could be to add column in your data and update it with required details for grouping. However, it is very tedious to add columns every time. Also it will increase number of columns in your data and make your database bigger.
You can use group option in pivot table to achieve the same results without adding any extra columns in your data. Just follow few steps mentioned below...

Select the items in the pivot
Right click inside pivot table
Click group
This will group selected data in pivot and give default name like Group 1, group 2 etc.
Change the group name

Steps

Simple...isn't it?

How to remove grouping:
Once you apply grouping on any field, excel pivot automatically adds one more field with the similar name as the filed in which the grouping is applied.
You can remove this grouping by simply dragging this field out of pivot table. This will remove this groping from the pivot table, however it will remain in the pivot field list. You can use the same in future if required.

To delete this field from the field permanently. Right click on the field which is grouped and click group. It will remove the additional grouped field.

Additional note:
Group option gives lot of additional features to group numerical or date fields e.g. grouping date fields into months and years etc. Simply right click on date or numeric field and you can explore more options.