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.