# Compare two columns and add third column based on matches

1. ## Compare two columns and add third column based on matches

I have two columns in two sheets each. I would like to compare those two columns for any match and where both columns have a match I would like to add the corresponding values in third column in one of the sheet.

Attached is an example of what I am looking to do.

Sheet 1 has three columns. I would like to match sheet1:columns with sheet2:row2 data (same color) AND sheet1:column2 data with sheet2:row1 data and for every instance where both match, I would like to add up sheet1:column3 entries.

After looking through this forum I though sumproduct can do this. My try in sheet2:cellN2 but problem is AND condition returns 12 enteries that it tries to multiply with single entry from sheet1:column3.

I guess what is making this complicated is that I would like to match each entries from sheet2 rows with corresponding columns and find all the matches not just one or first match.

This file is a very small version of my actual problem. On sheet1 I have 20,000 rows and on sheet 2 I have close to 300 double rows that I need to match (I am hoping to copy the formula down). So any split second efficiency will be big help also.

Does anyone have any solution or suggestion?

Jay

2. ## re: Compare two columns and add third column

Put this in Sheet2!A3
``Please Login or Register  to view this content.``
Drag this formula till Sheet2!K3, SUM(A3:K3).
This (in my opinion) cannot be one formula as then you search a range within a range and that will result in a 2-dimensional array. This can be done with VBA (User Defined Function) but not with Worksheet functions.

3. ## re: Compare two columns and add third column

Thank you for the reply. I know your formula will work but I actually need to compare the two dimension array as there might be some combinations from different columns of sheet2 columns. For example 519 and 51 (that combination may not exist in sheet1 right now but I know there are combinations like that in my original data).

Any idea how do I go about doing it through VBA? I will somehow need to fill in all 200 rows in sheet2 where I have my data. Obviously, I am not a pro in excel but if you show me the way I can try.

Jay

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1