Excel/Compare multiple columns together to determine the result to return
QUESTION: I have an Excel workbook with two worksheets. Sheet1 contains many columns and I want to compare three of them (A, B, C) with three similar columns (A, B, C) from Sheet2. Then, where there is a match I want to return the value in a fourth column (D) from the matching row in Sheet2 to a cell in a fourth column (D) on Sheet1. The rows in Sheet2 are all unique so there is only one possible match to a row on Sheet1.
In both Sheet1 and Sheet2: Column A is a number 1-10, Column B is one of two colors ("Red", "Blue"), and column C is one of four text options ("Cat", "Cat + 1", "Cat + 2", "Cat + 3"). Column D is populated on Sheet 2 with some value 100-300 that corresponds to the other values in its row.
I see the formula working like this, I just don't know how to write it: Where Sheet1 A+B+C = Sheet2 A+B+C, set Sheet1 D to Sheet2 D
ANSWER: I think I'd do this with a sumproduct function
This should return either a zero OR the row number of the matching row on sheet2 - you could EITHER put my formula in column E and use this as a driver for an IF field in column D (which would be my preference) or you could write the IF formula around it - but this would require doing the same calculation twice. Amend my 2:200 rows to whichever rows you actually need to test.
Question was found "abandoned" in the question pool.
---------- FOLLOW-UP ----------
QUESTION: I tried this and cannot seem to get any result other than 0. I attached a couple of example images to show what sheet1 and sheet2 look like. Sheet2 is longer than shown. It has every combination and continues through row 81. I am trying to get the score from the corresponding row in sheet2 into the right spot on sheet1. Hopefully this will help find a solution. Thank you.
I always test answers like this before posting to check I'm not mis-typing, so I know in principle it does work - though it needs exact matches - so any spaces would throw this out - difficult to see what may be wrong from images, but more than happy to look at a file - my email being firstname.lastname@example.org
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here