-
Archive of "Technical" Category
How to match values for two columns in Excel
One item that I see people struggle with quite often is how to match values for two different columns in Microsoft Excel. In many cases you can sort both rows, remove duplicates, and then look for a match. This however doesn’t preserve the original data.
I’ve also seen people write a program to parse the data and then run queries on it. This is efficient if you’re going to perform the task again and again, but not very time friendly if you only plan on doing it a few times. Fortunately some of these functions are built right into excel and you can match values on two columns with some simple calculations.
We’ll use two columns in this example, both containing the first names of an individual. The objective would be to determine if the value of the first column exists in the second column. This seems simple for the small number of entries we’ve laid out, so please consider you would be doing this with hundreds or even thousands of values.
Here we have our two columns that we would like to compare:
We’ll want to use the next column to set up a MATCH function covering the span of the second column. In this example, we have the name “MARY” in field A2, and would like to see if there is a match for anything in fields B2 to B19. We’ll enter a formula of =MATCH(A2,$B$2:$B$2:$B$19,0) into cell C2.
If you aren’t familiar with placing the “$” character in the formula box then just know that it keeps a value constant when cutting and pasting. We’ll want to create a formula for the C2 cell and then cut / paste it for all values in column C, so we want the “$” character in before the row/column places to ensure Excel doesn’t move it with the copy/paste function.
With this MATCH function we now get a value which indicates the first row where the Column A finds a match with column B. If no match is found then a “#N/A” is placed in the column.
We can then issue an IF function to detail some output on the data. In this example I used a “YES” if there was a match, but this could easily be replaced by a “1″ if you were trying to add the number of matches, etc. In our example we’ll want to use the formula “=IF(C2>0,”YES”)” in Row D:
Hopefully this helps any Excel column matching items you may want to accomplish.
