Excel Features Less Known For Entrepreneurs

An SMB should use every useful tool that makes sense; the critical test is to make sure the tool makes sense for your business and is not just an over-promoted feature that will take hours from your productive business hours. Here are some Excel features that are lesser known, but worthwhile for entrepreneurs to know.

Index and Match vs. VLOOKUP  

VLOOKUP is a highly featured function in Excel so much that it has become unfortunately a too simple measure to screen candidates out as to Microsoft Office abilities.  

The main issue with VLOOKUP is that it is one-eyed, it can’t look left in the spreadsheet.    

Excel offers two primary lookup methods: VLOOKUP and INDEX-MATCH.  Although the two ways are similar, INDEX-MATCH is the most powerful.  

The VLOOKUP function has these arguments:  

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)  

The range_lookup argument is optional, often overlooked, but very useful. This is because range_lookup determines whether VLOOKUP returns an approximate match or an exact match.  

Although Excel uses a different technique, here’s a simple way to think about how approximate matches work:  

Excel works its way down the first column until it finds a value greater than the lookup value. Then it backs up one row to return its results. If the lookup value is greater than the last number in the column, it delivers that last result.  

When Should You Use an Approximate Match?  

The only time you should use an approximate match is when you DEFINITELY need an approximate match. If you need an exact match, and if you want accurate results, NEVER use an approximate match.  

By default, VLOOKUP returns an approximate match. This is unfortunate, for two reasons. First, most Excel users need exact matches most of the time. Second, approximate matches are risky, because they can return false results.  If you need an exact match, and if you want accurate results, NEVER use an approximate match.

This figure illustrates a typical occasion when approximate matches are needed. The figure shows a schedule of quantity discounts. If a purchase is between one and four units, no discount is offered. A purchase from five through 24 units gets a 5% discount, and so on.

Excel’s Best Lookup Method: INDEX-MATCH  

The most powerful and flexible way to look up data in Excel is the INDEX-MATCH method. It relies on two Excel functions:  

=INDEX(reference, row_num, column_num)  

• reference—a range of cells  
• row_num—the row in reference from which to return data.  
• column_num—the column in reference from which to return data.  

If the reference is one row or column, the INDEX function can use this syntax:=INDEX(reference, cell_num)  

=MATCH(lookup_value, lookup_array, match_type)  

• lookup_value—The value to match in lookup_array.  
• lookup_array—A range of cells with data.  
• match_type—Specifies how Excel matches the lookup_value with values in the lookup_array. For exact matches, always use 0 for this argument.  

This blog has provided an overview of a critical data lookup feature in Excel.  It is vital to identify what is expected out of the Excel workbook before structuring the worksheets and formulas. Other formulas for small business owners to know can be found here.

At Cogent Analytics, we never stop looking for ways to improve your business and neither should you. So, check out some of our other posts for helpful business information: