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.
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:
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
From the above, you can see that the value in cell A8 was obtained by using the formula:
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…
- The INDEX lookup
Looking at the above you can see that the fourth value in the array is ‘Dick’
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.
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)
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
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.
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!
Thanks to Business Insider for the great lesson!