Thursday, October 28, 2010

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.

1 comment:

1. Some tips.
1. Insert an extra column between both tables to make some space.

2. Convert the ranges to tables (Format as table command on the ribbon)

3. No need for an array formula, just type this one into cell C2:

=MATCH(A2,\$E\$2:\$E\$6,0)

Because we typed it next to a range formatted as table, Excel will automatically expand the table with one column and also copy down our formula.

See: www.jkp-ads.com/articles/excel2007tables.asp

Ms. Excel- Resident Excel Geek