You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Compare multiple columns together to determine the result to return

Advertisement

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

=sumproduct(--(sheet2!$a$2:$a$200=sheet1!a2),--(sheet2!$b$2:$b$200=sheet1!b2),(sheet2!$c$2:$c$200=sheet1!a2),row(sheet2!$a$2:$a$200))

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 aidan.heritage@virgin.net

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

Excel

Answers by Expert:

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.**Education/Credentials**

I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!