PDA

View Full Version : Comparing multiple columns and copying cell



vahju
10-03-2005, 08:40 AM
Scenario:
Two spreadsheets. Both contain similar data (alphanumeric characters) but not exactly the same. The data in First three columns in both spreadsheets match. Need to copy over data in Sheet2 to Sheet1 in corresponding row based on criteria below. Each spreadsheet contains a couple hundred rows.

Problem:
Need to compare cells A1, B1, and C1 in both spreadsheets. If all three cells match in the same row in both spreadsheets then copy cell D1 from sheet2 into D1 of sheet1.

Logic (pseudo code):
If Sheet1.A1 equal to Sheet2.A1 and
Sheet2.B1 equal to Sheet2.B1 and
Sheet2.C1 equal to Sheet2.C1 then
Copy Sheet2.D1 to Sheet1.D1
Else
Leave blank.

I believe once the formula is created I should be able to fill it all the way down to get my results. I did some research and think the Index combined with Match function would work but I have no experience with these functions.

Any help would be appreciated.

Norie
10-03-2005, 12:30 PM
Wouldn't this work?


=IF(AND(Sheet1!A1=Sheet2!A1,Sheet1!B1=Sheet2!B1,Sheet1!C1=Sheet2!C1), Sheet2!D1, "")

vahju
10-03-2005, 08:09 PM
I did a little research (and by that i googled some keywords) and ended up a Mr. Excel's website. Turns out a Index(Match()) function example helpdesk with some major tweaks.

Link --> http://www.exceltip.com/st/Retrieving_an_Item_from_a_List_that_Meets_Multiple_Criteria/832.html

This is what I came up with:

{=INDEX($D$2:$D$1929,MATCH(A2&B2&C2,$A$2:$A$1929&$B$2:$B$1929&$C$2:$C$1929,0))}

Your If Then statement would only work if the 3 columns were in the same row in both sheets, which may have been some helpfull info that I left out.

So the above statement works except when the results are trying to copy over a empty cell or a cell that doesn't match any of the criteria. Is there a way that if none of the criteria is met then leave none in the cell or leave a blank cell.