You are here:

Excel/Sum Product formula

Advertisement


Question
Index, Match, If formula
Index, Match, If formu  
QUESTION: I'm using Excel 2010.

The Worksheet and Table are located on two different Sheets. In the yellow highlighted cells I manually entered the "answers".
I want to replace my manual entries with a formula that gives the same "answers".

I think I need INDEX and MATCH combined with IF.

Something like: "Match Ranch, Lot ID 1, Lot ID 2 on the Worksheet to the same on the Table. If there's a match, AND if the Designation is "Strawberry", give me a "Yes", otherwise blank."

I hope the attached image is readable this time.
Thank you for any help.

ANSWER: Donna,

Assuming The word "Ranch" in you example appears in cell A1 of sheet1 and "GT Ranch" appears in A1 of sheet2 then

in cell D2 of sheet1 put in the formula

=IF(SUMPRODUCT((Sheet2!$A$2:$A$50=$A2)*1,(Sheet2!$B$2:$B$50=$B2)*1,(Sheet2!$C$2:$C$50=$C2)*1,(Sheet2!$D$2:$D$50="Strawberry")*1)>0,"Yes","")

then drag fill cell d2 down to cell D10

That worked for me. (I had to add some data to sheet2 to get the matches you show, but when I did, then I produced the results you show. )

--
Regards,
Tom Ogilvy






---------- FOLLOW-UP ----------

Sumproduct followup
Sumproduct followup  
QUESTION: The formula works, but there's a glitch in my process and I get the "wrong" answer sometimes.

I've attached an image that I hope informs and illustrates.

Thank you for volunteering. I appreciate your help.

Answer
Donna,

Assume column C is the ID2 column then
If you want to match if there is a match in column C or (if column C in the Static sheet is blank and column C in the worksheet is not blank) then


=IF(SUMPRODUCT((Sheet2!$A$2:$A$50=$A2)*1,(Sheet2!$B$2:$B$50=$B2)*1,(Sheet2!$C$2:$C$50=$C2)+((Sheet2!$C$2:$C$50="")*($C2<>"")),(Sheet2!$D$2:$D$50="Strawberry")*1)>0,"Yes","")

That may work - but it also might cause matches that were previously not allowed.  Without have extensive knowledge of your data I can't say.

--
Regards,
Tom Ogilvy  
About Excel
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

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.