LOOKUP in Excel

Lookup in Excel

 

                Excel can produce varying results in a cell, depending on conditions set by user. Today we will learn about LOOKUP function in Excel.

Before we actually start to use the various LOOKUP functions, it is worth learning the terms that we will come across, what they mean and the syntax of the functional arguments.

Vector Lookup

A Vector is a series of data that only occupies one row or column. Lookup will look through this row or column to find a specific value. When the value is not found, a corresponding result in the adjacent row or column is returned.

 

The syntax for LOOKUP is as follows =LOOKUP(lookup_value,lookup_vector,result_vector)

Lookup Value represents the number or text entry to look for

Lookup Vector is the area in which to search for the LOOKUP_VALUE

Result Vector is the adjacent row or column where the corresponding value or text is to be found.

There is one condition apply in LOOKUP that data should be placed in ascending order. E.g Numbers from lowest to highest, text from a-z. If this is not done, the LOOKUP function may return the wrong result.

 

We are providing a case which will help you to use LOOKUP function.

We have a company called ABC and they have to provide Diwali Bonus to employees based on target achieved. Based on target company will gift a Car to employee. In this scenario we can create LOOKUP function to make work easy.

Lookup Value will be target achieved by employee (Target Achieved Column in pic)

Lookup Vector will be the column where we have defined the target (Cell A2 to A12)

Result Vector will be the column where we have defined the Gift. (Cell B2 to B12)

 

 

Now if we will enter the target achieved in Cell D2, LOOKUP function will return that which car we should gift to employee.

There will be multiple scenario where you can use LOOKUP function and save your time

 

 

 

Keep Reading

Sanjay Bakshi

mesanjaybakshi@gmail.com

 

IFERROR In Excel

What is IFError?

A common request in the area of functions is something to simplify error checking.

e.g If user wants to catch the errors in VLOOKUP and use their own error text opposed to excel error, they can use IF and ISERROR functions or user can use only IFERROR.

Here are both the examples.

1) In first example we will try to get Name Value from left grid by using IF and ISERROR.

 =IF(ISERROR(VLOOKUP(D5,A$2:B$6,2,FALSE)),"Value is not Availble in Source Table",VLOOKUP(D5,A5:B9,2,FALSE))

 

 2) For same scenario we are using IFERROR.

=IFERROR(VLOOKUP(D7,A2:B7,2,FALSE),"Value is not Availble in Source Table")

 

 

As you can see that both are providing same result, but user need to repeat the VLOOKUP formula twice. This has a number of problems

a) First, it is hard to maintain.. if you have to change the formula you have to do two time

b) Second, it can affect the performance because formulas are quite often run twice.

 

Keep Reading and Happy Diwali

Sanjay Bakshi

mesanjaybakshi@gmail.com

 

 

Slicer in Excel 2010

In earlier versions of Microsoft Excel, we can use report filters to filter data in a PivotTable report, but it is not easy to see the current filtering state when you filter on multiple items. In Microsoft Excel 2010, you have the option to use slicers to filter the data. Slicers provide buttons that you can click to filter PivotTable data. In addition to quick filtering, slicers also indicate the current filtering state, which makes it easy to understand what exactly is shown in a filtered PivotTable report.

Slicers are easy-to-use filtering components that contain a set of buttons that enable you to quickly filter the data in a PivotTable report, without the need to open drop-down lists to find the items that you want to filter.

When we use a regular PivotTable report filter to filter on multiple items, the filter indicates only that multiple items are filtered, and we have to open a drop-down list to find the filtering details. However, a slicer clearly labels the filter that is applied and provides details so that you can easily understand the data that is displayed in the filtered PivotTable report.

Slicers are typically associated with the PivotTable in which they are created.

When we create any slicer we get the following elements

1. A slicer header indicates the category of the items in the slicer.

2. A filtering button that is not selected indicates that the item is not included in the filter.

3. A filtering button that is selected indicates that the item is included in the filter.

4. A Clear Filter button removes the filter by selecting all items in the slicer.

5. A scroll bar enables scrolling when there are more items than are currently visible in the slicer.

 

 

Selection of Slicer

 

 

Slicer Output

 

 

Keep Learning

Sanjay Bakshi

mesanjaybakshi@gmail.com

Excel 2010 - Some Important Information

Microsoft Excel is a spreadsheet application developed by Microsoft for Microsoft Windows and Mac OS. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. Microsoft has done minor enhancements and 64 bit support also

 

ü You can undo the last 100 actions

ü Each worksheet holds 1,048,576 rows by 16,384 columns

ü There are 1,024 global fonts available to use, 512 per worksheet

ü Zoom range is from 10 percent to 400 percent

ü You can select 2,147,483,648 cells that are not touching

ü You can have up to 255 arguments in a function

ü You can nest 64 levels of functions per formula

ü You can have up to 255 data series in one chart

ü You can highlight 32,767 cells per worksheet

ü Multi-threading recalculation (MTR) for commonly used functions

ü Improved pivot tables

ü More conditional formatting options

ü Additional image editing capabilities

ü In-cell charts called spark lines

ü Ability to preview before pasting

ü Many new formulas, most highly specialized to improve accuracy

 

Keep Learning

Sanjay Bakshi

mesanjaybakshi@gmail.com

 

Sparkline in Excel 2010

A Sparkline is a small chart that is aligned with rows of some tabular data and usually shows trend information.

1.     Select the data from which you want to make a Sparkline.

2.     Go to Insert > Sparkline and select the type of Sparkline

3.     Specify a target cell where you want the Sparkline to be placed

4.     Optional: Format the Sparkline if you want.

There are 3 basic types of Sparkline in Excel 2010.

1.     Line chart

2.     Column chart

3.     Win-loss chart.

 

 

Selection of Sparkline (Last Set)

 

Data Selection for Sparkline

 

Sparkline Formatting

 

Keep Learning
Sanjay Bakshi
mesanjaybakshi@gmail.com
 

Unique Records in Excel

Remove Dupes in a column: Data Menu>Filter>Advanced Filter. Check Copy to another location (select a Copy To range). Unique records only.

Thanks,
Sanjay Bakshi
mesanjaybakshi@gmail.com

@@ RowCount

@@ RowCount

It returns the number of rows affected by the last statement. If numbers are more than 2 billion then we have to use @@ ROWCOUNT_BIG.

The return value of this will be in Integer data type.

USE <DB Name>

GO

UPDATE <Table Name>

 SET <Column Value> = “Value”

IF @@ROWCOUNT = 0

PRINT 'Warning: No rows were updated';

GO

USE <DB Name>

GO

Insert into <Table Name> (Col1, Col2) values (‘Val1 for Col1’,’ Val1 for Col2’)

Select @@RowCount

GO

Keep Learning

Sanjay

mesanjaybakshi@gmail.com

Share Point Installation

Hi All,

Please find the Installation Details of Share Point.

https://drive.google.com/file/d/0B13PrmqQdIU_Tm5XOEhrbGZFWFk/view?usp=sharing

Thanks,
Sanjay Bakshi

Share Point - Part 2 Hardware Requirement

SharePoint 2013 provides for several installation scenarios. Currently, these installations include single server with built-in database installations, single-server farm installations, and multiple-server farm installations. This article describes the hardware requirements for SharePoint 2013.

Web server or Application server

• 12 GB RAM
• 64-bit, 4 cores Processor
• Hard Disk Space of 80 GB for system drive

Database server

• 8 GB RAM for Small Deployments (Applicable if users are less than 1000)
• 16 GB RAM for Mid Size Deployments (Applicable if users are less than 10000)
• 64 Bit with 4 Core for Small Deployments (Applicable if users are less than 1000)
• 64 Bit with 8 Core for Mid Size Deployments (Applicable if users are less than 10000)
• Hard Disk is depends on how much content that we have for deployment 

Happy Reading

Sanjay Bakshi

mesanjaybakshi@gmail.com

Share Point - Part 1

What is Share Point?

Share Point is web application framework/platform developed my Microsoft. It was first launched in 2001.

It help to connect people and the information. It provide the central site for sharing information with other users. It is accessible from anywhere in the world via Internet Connection.

This provides places to store and share ideas, information, communication and documents. The sites facilitates team participation. The document libraries allow for easy checking in and checking out of documents and version control.

As share point can have multiple sub sites. Similar to storing files in folder we can them in different share point sub sites.

A typical share point web site may include below information such as

1) Discussions
2) Calendars
3) Contacts
4) Task List
5) Documents

This sites can be easily searched and user can get the intimation whenever any document has been changed.

It has started with Content Management and document management but later version came up with new and broader capabilities.

Happy Reading
Sanjay Bakshi
mesanjaybakshi@gmail.com