INDEX/MATCH vs. VLOOKUP

I have a group called Excel Enthusiasts (catchy title, eh?) on LinkedIn that I started several years ago.  It currently has over 16,000 members from over 60 countries.  Recently, one of the leading discussions has been on which is better, VLOOKUP or INDEX/MATCH. 

Clearly, VLOOKUP is by far the most frequently used function in this instance, primarily because it is the more unassuming formula, but also because most Excel users simply don’t understand how to use the INDEX/MATCH combination.

The major drawback of VLOOKUP is it requires a static reference in the form of the first column.  Not very cool.  INDEX/MATCH on the other hand, is much more flexible, allowing you use whichever column you choose for your reference.  Much cooler.

Let’s take a look at a simple example using the Illustration below:

Let’s say you wish to create a Code Retriever.  MATCH returns the Row Number of the location in an array of value you specify. Using the example above, the number “4” is returned by the formula:

=MATCH("Tampa", $A$2:$A$8,0)

INDEX, on the other hand, returns the Value that you identify by row number in an array. Using the example above, “Tampa” is retuned by the formula:

=INDEX($A$2:$A$6,4)

Combining the INDEX and MATCH functions is where the Real Power comes in. Let’s say we want the Code for San Diego. We can set up a Code Retriever as shown in cells E1:F2, by inserting the following formula in cell F2 (This will return "141"):

=INDEX($C$2:$C$8, MATCH(E2, $A$2:$A$8, 0))

Using the MATCH and INDEX functions together is truly a Powerful way of extracting data when you need it. Once you try it, you may never go back to VLOOKUP again!

Validation is Good Stuff...

I am currently teaching an Excel class to a fine group of intermediate-advanced users.  One of the topics we are reviewing is the many uses of Validation. 

Validation can be useful in many arenas, including the maintenance of Integrity of your data, (we’ve all heard of GIGO – Garbage In Garbage Out), convenient Drop Down Boxes, informative Popup Alerts, and much more.

Maintaining Integrity

If you know that only certain data should be entered in a range, you can easily control this by using one of the many controls in the Allow dialogue drop down.  You can control size or range of numbers, text considerations or, as in the example below, which words can be entered (in this case, True or False…).

Drop Down Boxes
Drop down boxes can add interactivity and professionalism to your worksheets.  By choosing List in the Allow box, you can select a range of cities, employees, customers, companies, whatever you find useful.  In the example below, as list of sales representatives was selected.

Popup Alerts

By using boxes included on the Input Message tab, you can create a Popup Message whenever a user clicks on a cell within the range this Validation is used.  This can be further useful in assuring that the proper information is being put into your database, and it helps jog the memory of the user entering the data.

Validation.  Many uses, many benefits.  Really Good Stuff!

Frequency Functions and Tables

Array Functions are one of those categories of Excel tools that most Excel users have never heard of, and mystifies most of those who have.

These mysterious functions can be quite useful, however, and when you learn how they are activated, you will laugh at how easy (albeit unusual) they are to use.

If you want to do some analysis on how frequently values within particular ranges occur (rather than looking at each individual occurrence), for instance, an excellent solution in Excel is the Frequency Function.  This valuable tool works as an Array Function that counts the number of values that occur in each specified interval (or “Bins”, as they are typically called).

The FREQUENCY Function syntax is as follows:
        FREQUENCY(DataArray, BinsArray)

The function utilizes a database for a source, and is entered as an Array formula after you select a range of adjacent cells (B2:B7 in our example) into which you want the distribution to appear. After you select the data and bins arrays, press CONTROL+SHIFT+ENTER (this is what mystifies many users…).

The following example shows the “Bins” under the heading Size and the Frequency of claims within each range under Occurrences:

Special Note: The number of elements in the returned array is one more than the number of elements in bins array. The extra element in the returned array returns the count of any values above the highest interval.

Illustrations of key applications of the Frequency Function will be explored in future blogs that will be published soon.  In the meantime, take a 5-minute look at this vital tool.  You may find that it is not all at mysterious…

AutoFilter Tricks

This is a great little technique to start your year of Excel use off right.  The AutoFilter tool is a sometimes underused and under-loved convenience that has some built-in tricks that many Excel users are simply unaware of. 

Let’s look a Totally Cool example:  First select any cell within your table or database, go to Data, choose the Filter menu, and click on AutoFilter. AutoFilter Arrows will appear at the top of each column, allowing you to filter on whichever column you choose.

Okay, good.  Most of us know all about that, but what kind of Cool Tricks can you do after that? 

Well, let’s say you want to filter/sort a table which includes the sales figures for several reps and regions.  If one of your regions is called SouthEast and you want to see just the sales from that part of the country, for instance, you can simply right-click on any cell which has “SouthEast” in it, and choose Filter by Selected Cell’s Value from the secondary Filter menu.

 

Bamm!  Your data is instantly filtered to show only that region!

Not cool enough for you?  As you can see by the screenshot above, you can also use this technique to filter by the cell’s Color, Font Color, or Icon!  All in the blink of any eye! 

You can also create a great variety of Custom AutoFilters to manipulate your data, but for On-the-Fly Wizardry try one of these handy right-click approaches and see how many of your Excel user comrades raise their eyebrows.  As I always say in my classes, “Right-Click (with Filtering or most other things in Excel) is Our Friend!”

 

Happy New Year, All!