Index Match Match is not returning secondary criteria

[Twiggy]

Hi Excel Ninjas,

Good morning. I've enjoyed reading the posts on this forum, and I've found them very helpful. I think I'm close to a solution here, but I haven't been able to get this formula to work for this situation.

I expected this formula to return the numeric value 1,116 in sheet 'Data_File' cell R12, but it returns a zero. The value I expect it to return (1,116) should be retrieved from sheet 'BCS' cell K3. The other value returned in 'Data_File' cell O12 is correct, and only some of the products listed in the sheet 'BCS' will have two rows like this one.

How can I make this formula smart enough to return that value?

My existing formula is:
=IFERROR(INDEX(BCS!$A$2:$BF$112,MATCH($A12&$C12&$E12,BCS!$A$2:$A$112&BCS!$B$2:$B$112&BCS!$C$2:$C$112,0),MATCH(R$9,Table2[#Headers],0)),0)

My Excel file Test(1).xls is attached.

Thank you in advance for your time!

Twiggy

 

[Chihiro]

That's becasue there's 2 rows matching your criteria.


Match function will return first matching result from array. In this case, row 1, column 11. Which is "0".

You need to check for criteria which differentiate row 1 & 2 in addition to your current criteria.

 

[Twiggy]

Hi Chihiro,
Thank you for the quick reply. I understand I need to add another check, but I don't know how to do that.
The criteria that differentiates the two rows is the Pack Size, and I don't have that criteria in the 'Data Sheet'. Can I add an if/then statement? Can you point me to an example that might be helpful?
Thank you,
Twiggy

 

[Chihiro]

One question.

Is there always 1 row with data or could both rows have data for each column in Table2?

If both could have data, what number do you need to pull to Data_File?

 

[Twiggy]

Some products will have 1 row in Table2.
Others will have 2 rows in Table2.
If there are two rows, only one record per week will have a number >0.
If both could have data, I need to pull the number that is >0.

 

[Chihiro]

With your condition... I'd do something like below. Confirmed as array (CSE).
=SUMPRODUCT(INDEX(IF((Table2[WH]=$A12)*(Table2[Customer Ship To Number]=$C12)*(Table2[Product Number]=$E12),Table2[[2016*01]:[2016*52]]),,MATCH(N$9,Table2[[#Headers],[2016*01]:[2016*52]],0)))

Edit: Or just SUM instead of SUMPRODUCT.

 

[Twiggy]

It works. Thank you Chihiro.

 

[Twiggy]

Hi Excel Gurus, I am not sure if I should be replying to this thread or if I should start a new one. I'm posting here for now because my question is related to this thread. The formula Chihiro provided me with on Oct 26, 2016 works for the file I was using, but I've been asked to create a similar file and I'm having difficulty getting the same formula to work. The problem is the formula either returns a value error or a zero.
The original formula Chihoro provided is the Data_File tab, cell N12. It's a SUMPRODUCT formula that looks at the BCS tab and adds up the totals for each week. I'm trying to replicate this formula in Sheet1, starting at cell O11. I'm expecting it to add up the totals each week in Sheet2, Table1. My expected result in Sheet1, cell O11 is 20160. The expected result for Sheet1, cell P11 is 60320. I am confirming the formula using CTL+SHIFT+ENTER. I've uploaded my file. I appreciate your help. Twiggy