Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

LinkBack Thread Tools Display Modes
Old 08-03-2014, 07:10 AM
gbaker gbaker is offline Windows 7 32bit Office 2010 32bit
Competent Performer
Join Date: May 2012
Posts: 111
gbaker is on a distinguished road
Default match two columns of data

I need a formula that will match 2 columns of data. The numbers I want to match are in different columns and in different rows. What formula would find the matches in both arrays and put a 1 next to the ones that match. see example spreadsheet attached.
Attached Files
File Type: xlsm TEST MATCH.xlsm (9.6 KB, 3 views)
Reply With Quote
Old 08-03-2014, 04:01 PM
whatsup whatsup is offline Windows 7 64bit Office 2010 32bit
Competent Performer
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough


Use COUNTIF() and just count the value of a cell within a column:
B3 =COUNTIF($D$3:$D$31,A3)
E3 =COUNTIF($A$3:$A$31,D3)
Copy the formulas to the end of your list. It will return 0, if there's no match.

You are using a text-format for numbers! I wonder why. Be aware that the above formulas if looking for a text won't find a match in case the same value exists as number (and viceversa). It will return 0.
Reply With Quote

Thread Tools
Display Modes

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro for Columns to Row Data brunssl2 Excel 3 04-24-2014 12:16 PM
From an XL sheet ,how to keep the group of columns which match with other XL sheet Zubairkhan Excel 2 03-04-2014 10:57 PM
Match information from 2 different columns gbaker Excel 2 08-18-2012 06:07 PM
Match two sets of data and display specific data lolly150 Excel 1 05-14-2012 10:33 PM
If two geographical data match in two sheets, copy unique id/code found in one sheet alliage Excel 1 09-01-2011 05:23 AM

All times are GMT -7. The time now is 10:35 PM.

Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc. is not affiliated with Microsoft