Excel Tricks that will let you stand out from the crowd – Part One

In my opinion, Microsoft Excel is one of, if not the most powerful software application out there.

It’s used by all types of companies in all types of industries, and can produce some impressive results. Being an ‘Excel Jedi’ has become a good way to ensure an income in the corporate world today.

There are a lot of basic shortcuts, formulas and tricks that most of us know already, but there are also a few gems that will help you to get the edge and  stand out from the crowd…

This is the first installment on a series of posts that will give insight and help you to stay ahead of the pack.

excel-icon

The VLOOKUP Function

VLOOKUP is a function that allows you to ask Excel to search a table and find a specific column’s value. Handy when you need to find a number quickly during a meeting.

The format is:

=VLOOKUP(VALUE,ARRAY,COLUMN,RANGE)

VALUE = What value to search for

ARRAY = The range of cells to search in

COLUMN = The column that holds the value you are searching for

RANGE = Set to zero (type the number ‘0’) to specify that you don’t need an exact match

EXAMPLE:

excel1From the above, you can see that the value in cell A8 was obtained by using the formula:

VLOOKUP=(“Harry”,A2:E4,5,0)

So, if we break this down:

“Harry” = VALUE =  Look for Harry

A2:E4 = RANGE = the selected table

5 = COLUMN = the fifth column in

0 = RANGE = Any/All (no exact match)

While VLOOKUP is a pretty cool function to use, it has it’s limitations:

  • It can only search from left to right, for example, meaning that it only looks a values in the left most column of the array. If you wanted to look up a value to the eft of this, you would need to reconfigure everything. For example if you wanted to get the name of the person who had 98 in ‘Result 3’, VLOOKUP is not good.
  • Running VLOOKUP on a large spreadsheet can take a long time

There are two functions that you need to learn in order to run a faster and more flexible lookup…

  1. The INDEX lookup

Format: INDEX(array,n)

excel2

Looking at the above you can see that the fourth value in the array is ‘Dick’

=INDEX(A2:A4,2)

Array = A2 to A4

N = Number in the array to check = 2

2. The MATCH function

The MATCH function will give you the INDEX of a particular value in an array.

excel3

From this example, you can see that the MATCH function searched the array A2 to A4 for the VALUE “Tom”, and returned the result showing the position of “Tom” (First)

=MATCH(VALUE,ARRAY,RANGE)

VALUE = The value you are looking for (Tom)

ARRAY = The cell array that you want to search within (A2:A4)

RANGE = 0 (Any/All)

OK, so MATCH and INDEX don’t do a lot on their own, but if you combine them , they become a lot more powerful, making VLOOKUP look like a complete waste of time!

This is because you are joining the power of INDEX to return the value of an index, with MATCH’s power to find an INDEX. Make sense? The formula will look like this:

=INDEX(Column,MATCH(Search Value, Search Column,0)

In the example below, I wanted to find out who supplies my bananas.

=INDEX(B2:B8, MATCH(“Bananas”, A2:A8,0))

B2:B8 = Search Column

Bananas = Search Value

A2:A8 = Result or output column

0 = All/Any

excel4

The MATCH function finds the INDEX for me, and the INDEX function uses that result on the specified array of cell in order to find and display the matching entry. (Farmer Smith)

You can perform a whole truckload of functions using this formula, and if you run it on a large spreadsheet, it is also a lot quicker than VLOOKUP.

excel5

Well, that’s the first lesson. Feel free to leave a comment or request a particular how-to for Excel.

For advanced statistic training in Excel, Click Here!

 Earn money by doing online surveys!!!

Thanks to Business Insider for the great lesson!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s