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.

 

[Marcelo Branco]

It should have worked - we need a small data sample to see why it's not working.

Possible issue
You are using a different range in the second condition
RIGHT('Sep Corn Stocks'!$C$2:$C$5300,3)=Corn!$C$2
Shouldn't it be RIGHT('Sep Corn Stocks'!$C$2:$C$53,3)=Corn!$C$2 ?

M.

 

[DR RENO]

I've expanded them so if the data export has additional rows, it won't cause the cell to error out. When you sent me the formula correction for 2 variables, I added two zeros to the range and it pulled in the expected values. This range is for the Month. The export data comes in a text string, ie (First Part of DEC). I've put it in index/match before as a single variable criteria and it did work as an array.

 

[DR RENO]

I went back into my spreadsheet and saw what you were alluding to previously. I've updated the range to reflect 5300 rows, but still receiving same issue. It's populating 0 for each state's value

 

[Marcelo Branco]

Have you updated all the ranges to rows 2:5300?

M.

 

[DR RENO]

Yes

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

 

[Marcelo Branco]

As i said on previous posts we need a small data sample for testing purposes,
A last question: are there blank cells in 'Sep Corn Stocks'!$T$2:$T$5300 ?

M.

 

[DR RENO]

Ok, will provide one. I'm new on here. What is the easiest way to facilitate that (Small Data Sample)?

In the full named range, $T$2$:T5300 there are no blank cells, but there are text fields for state's that didn't disclose information. Those cells display as (D)

 

[Marcelo Branco]

To provide a small data sample: see Section B in
https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

M.

 

[DR RENO]

Hi Marcelo -

I had to download DROPBOX on my pc and then format file for you. In doing so, I saw a 0 in one of the Month source cells. Once I corrected it, everything populated.

I'm now officially good to go, thanks to you