Index/Match With Multiple Criteria Giving #NA

[DR RENO]

[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD]
[TABLE="width: 1130"]
<tbody>[TR]
[TD="width: 184, bgcolor: transparent"]=LEFT(C1,4)
[/TD]
[TD="width: 1322, bgcolor: transparent"]2012/2013
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]DEC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Montana
[/TD]
[TD="bgcolor: transparent"]=INDEX('Sep Corn Stocks'!$A$2:$T$53,MATCH(1,'Sep Corn Stocks'!$F$2:$F$53=Corn!B3)*('Sep Corn Stocks'!$B$2:$B$53=VALUE(TRIM(CLEAN($B$1)))),20)
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]North Dakota
[/TD]
[TD="bgcolor: transparent"]=INDEX('Sep Corn Stocks'!$A$2:$T$53,MATCH(1,'Sep Corn Stocks'!$F$2:$F$53=Corn!B4)*('Sep Corn Stocks'!$B$2:$B$53=VALUE(TRIM(CLEAN($B$1)))),20)
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Minnesota
[/TD]
[TD="bgcolor: transparent"]=INDEX('Sep Corn Stocks'!$A$2:$T$53,MATCH(1,'Sep Corn Stocks'!$F$2:$F$53=Corn!B5)*('Sep Corn Stocks'!$B$2:$B$53=VALUE(TRIM(CLEAN($B$1)))),20)
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]South Dakota
[/TD]
[TD="bgcolor: transparent"]=INDEX('Sep Corn Stocks'!$A$2:$T$53,MATCH(1,'Sep Corn Stocks'!$F$2:$F$53=Corn!B6)*('Sep Corn Stocks'!$B$2:$B$53=VALUE(TRIM(CLEAN($B$1)))),20)
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]=SUM(C3:C6)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I have independently conducted index/match function with each, and it produced expected result. However when I try to do two variables, and ultimately three, it will not produce a value. Really need help from someone, as this has caused me problems for 3 1/2 days, although that is quite embarrassing to admit.

 

[DR RENO]

So I changed to SUMPRODUCT, and now can get value to display....but it's incorrect. Also, it's giving the same incorrect value for each cell in the array. When I go into the function, it does show that it's calculating the correct value...just won't display. Help


[TABLE="width: 1130"]
<colgroup><col width="184" style="width: 138pt; mso-width-source: userset; mso-width-alt: 3364;"> <col width="1322" style="width: 992pt; mso-width-source: userset; mso-width-alt: 24173;"> <tbody>[TR]
[TD="width: 184, bgcolor: transparent"]=LEFT(C1,4)[/TD]
[TD="width: 1322, bgcolor: transparent"]2012/2013[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]DEC[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Montana[/TD]
[TD="bgcolor: transparent"]=INDEX('Sep Corn Stocks'!$A$2:$T$53,SUMPRODUCT('Sep Corn Stocks'!$F$2:$F$53=Corn!B3)*('Sep Corn Stocks'!$B$2:$B$53=VALUE(TRIM(CLEAN($B$1)))),20)[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]North Dakota[/TD]
[TD="bgcolor: transparent"]=INDEX('Sep Corn Stocks'!$A$2:$T$53,SUMPRODUCT('Sep Corn Stocks'!$F$2:$F$53=Corn!B4)*('Sep Corn Stocks'!$B$2:$B$53=VALUE(TRIM(CLEAN($B$1)))),20)[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Minnesota[/TD]
[TD="bgcolor: transparent"]=INDEX('Sep Corn Stocks'!$A$2:$T$53,SUMPRODUCT('Sep Corn Stocks'!$F$2:$F$53=Corn!B5)*('Sep Corn Stocks'!$B$2:$B$53=VALUE(TRIM(CLEAN($B$1)))),20)[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]South Dakota[/TD]
[TD="bgcolor: transparent"]=INDEX('Sep Corn Stocks'!$A$2:$T$53,SUMPRODUCT('Sep Corn Stocks'!$F$2:$F$53=Corn!B6)*('Sep Corn Stocks'!$B$2:$B$53=VALUE(TRIM(CLEAN($B$1)))),20)[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]=SUM(C3:C6)[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 565"]
<colgroup><col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;"> <col width="661" style="width: 496pt; mso-width-source: userset; mso-width-alt: 24173;"> <tbody>[TR]
[TD="width: 92, bgcolor: transparent"]2012[/TD]
[TD="width: 661, bgcolor: transparent"]2012/2013[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]DEC[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Montana[/TD]
[TD="bgcolor: transparent"] 265,327,000 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]North Dakota[/TD]
[TD="bgcolor: transparent"] 265,327,000 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Minnesota[/TD]
[TD="bgcolor: transparent"] 265,327,000 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]South Dakota[/TD]
[TD="bgcolor: transparent"] 265,327,000 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]1,061,308,000[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Try using 0 as the last argument of MATCH (exact_match), that is
=INDEX('Sep Corn Stocks'!$A$2:$T$53,MATCH(1,condition1*condition2,0),20)
Ctrl+Shift+Enter

M.

 

[DR RENO]

Thanks for responding. Tried that, but it's telling me it's a circular reference.

INDEX('Sep Corn Stocks'!$A$2:$T$53,MATCH(1,'Sep Corn Stocks'!$F$2:$F$53=Corn!B3)*('Sep Corn Stocks'!$B$2:$B$53=VALUE(TRIM(CLEAN($B$1))),0),20)

 

[Marcelo Branco]

A circular reference? I don't understand what is going on
In what sheet/cell are you inserting the formula?

M.

 

[Marcelo Branco]

A small data sample along with expected results would be helpful.

M.

 

[Marcelo Branco]

See if this works - observe the )

=INDEX('Sep Corn Stocks'!$A$2:$T$53,MATCH(1,('Sep Corn Stocks'!$F$2:$F$53=Corn!B3)*('Sep Corn Stocks'!$B$2:$B$53=VALUE(TRIM(CLEAN($B$1)))),0),20)
Ctrl+Shift+Enter

M.

 

[DR RENO]

Marcelo, you my friend are awesome!!! 4 days on this and you provided the fix!!! Everything is now coming in as expected. You don't know how grateful I am right now. Can I buy you a beer for your time and patience? Here's my personal email if you have a paypal account.

Reno_Martinez_27@Yahoo.com

Thanks again!

 

[Marcelo Branco]

DR RENO

You are very welcome. Glad to help

No payment needed. We are volunteers here trying to help.


M.

 

[DR RENO]

Well you are a terrific guy and I appreciate everything the people like you are doing here. While you're on this thread, can you take a look at what's going on when I introduced the 3rd criteria. It's showing the function is reading the last 3 of the Month correctly, but it's populating cell as 0 and then the same for every other cell in the array. Here's the formula I'm using.

=INDEX('Sep Corn Stocks'!$A$2:$T$53,MATCH(1,('Sep Corn Stocks'!$F$2:$F$53=Corn!B3)*(RIGHT('Sep Corn Stocks'!$C$2:$C$5300,3)=Corn!$C$2)*('Sep Corn Stocks'!$B$2:$B$53=VALUE(TRIM(CLEAN($B$1)))),0),20) with the array shift-ctrl-enter