Excel/Sum Product formula
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.
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
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. )
---------- FOLLOW-UP ----------
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.
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
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.