Match Function

I'm putting some stuff together for a presentation I am doing for the Institute of Internal Auditor's Indianapolis chapter in November and thought I would talk about the MATCH Function. The MATCH function can be very useful if you want to compare to lists.
In the example below, I have a current inventory list and an obsolete inventory list. I want to compare the 2 lists and make sure that there are no obsolete parts on the current inventory list.

In Column A I have the current part numbers and in column D I have a listing of obsolete parts.
In column G, I want to double-check to make sure that an obsolete part is not on the current list in Column A. So, I select G2 through G6 as this is going to be an array formula and type =MATCH(A2:A6,D2:D6,0) and then I press Control +Shift+ Enter, also called CSE. G2 through G6 are populated either with N/A messages or as you can see in G3 a number.   The number 4 in G3 is telling us that A3 has a match and that it is the 4th value in the other list.
 If you look down the obsolete part list, you can see that the 4th value of XYZ-124 is the same as in the second row of the new inventory list.  Now, this gets the work done but it doesn't really look that great with all the N/As and if you had a long list it would be hard to differentiate the error messages from the numbers.

We can make this more elegant by putting the MATCH function inside of IFERROR, a new Excel 2007 function.  In the example below, I selected G2.G5 again and typed =IFERROR(MATCH(A2:A6,D2:D6,0)," ") and then pressed CSE. All the error messages now display as a blank so it is much easier to see the match.

Free Excel CPE Course

Sorry- I just realized that I told a lot of different people about the free Excel CPE  we are offering at CPASelfstudy.com but I forgot to blog about it here!  I know, I couldn't believe I did that either!

Anway, we are offering a FREE... totally FREE... 1 hour Excel CPE course. Everyone always seems to be short that one odd hour.

The course is entitled, "Audit and Analytical Uses for Excel 2007 Filters and Tables. It is a brief overview course that focuses on Filtering data, Conditional Formatting and using Tables. It also briefly touches on Pivot Tables. So far, the reviews have been good. So, if you are a CPA needing CPE credit and want to learn something that you can immediately start using in your job, definitely check it out.
And of course, feel free to tell your friends and associates about it.
The link to the course is http://cpaselfstudy.com/item_185/Audit-Analytical-Uses-for-Excel-Filters-Tables-Free.htm

Evaluating Formulas

In the past, whenever I have talked about debugging or evaluating a formula, I have always talked about the Evaluate Formula icon found on the Formula ribbon

It is very useful as it allows you to break apart complex formulas step by step. Click Evaluate to examine the value of the reference that is underlined. The result of that evaluation is shown in italics.

If the underlined part of the formula is a reference to another formula, click Step In to display the other formula in the Evaluation box. Click Step Out to go back to the previous cell and formula.  And then just keep going until you have worked your way through the entire formula.

If it is a long complex formula, I still think this is the best method but I learned a quicker method the other day on Chandoo's site and I think it is really useful if you are in a hurry or the formula is relatively short.

( If it is a long complex formula, I think it is easier to see the breakdown in the Evaluate Formula dialog box. - just my personal preference.)

 All you need to do is double-click on the formula, select the component that you want to evaluate and press F9. If you have additional formulas, select them and press F9 again. When you are done, press ESC to get out.

Two ways to do the same thing... and yes, if we look around, we will probably find a 3rd method too. Have a good day.

Using Data Ribbon to Find the Top 5 Items

Below is an excerpt from the new course we are offering entitled "Audit and Analytical Uses for Excel Tables and Filters. It is a free course and has 1 Hour of CPE associated with it. Check it out at http://cpaselfstudy.com/item_185/Audit-Analytical-Uses-for-Excel-Filters-Tables.htm

Finding the Top 5 Invoices

For audit purposes, assume that you wanted to manually select the 5 largest invoices for testing. In this case, you would sort the Sales_Amt. column  by clicking the Sort icon on the Data tab and selecting “Sort Largest to Smallest”. The top portion of the result is displayed below. Notice that all the related data is also moved.

That was the quick and dirty way and works if the data list is relatively small. If the list was larger, you would want to use a Number Filter to identify only the Top 5 invoices. In this case, you would click the drop down box next to Sales_Amt and choose “Number Filters”, then select “Top 10…” as shown below.

Using the arrow keys next to the number “10”, click on the down arrow until the number “5” appears. This will filter the list to the Top 5 items. It will now look like the figure below: