Unable To Use Match Function With Export Data Point

[DR RENO]

Exporting USDA data for a proprietary spreadsheet , but unable to match the 4-digit Year column data formatting with it's appropriate reference cell. Other column data matches (State, Commodity, etc). Character length is equal as they each are four characters. However the cells do not equal unless I use the Trim function.

I tried the trim/clean copy and paste values with no success. If I nest Trim function inside Match it doesn't math. CODE=50

Any tips? Side note - Ultimately I will be using multiple match criteria for three variable (Year, State, Period). For the latter I need to use a LEFT function array to capture the last three characters. That formula does work for me.

1st time posting, but have reviewed many of your posts for years to solve Excel and VBA troubles. You guys are pretty amazing

 

[RasGhul]

Hi Reno,

You can confirm real numbers in a column with;

=isnumber(a1)

I like to use the following to remove spaces and convert to number;

=VALUE(TRIM(CLEAN(A1)))

 

[DR RENO]

That fixed my problem!! Thank you so much for your help RasGhul!!!

 

[DR RENO]

[TABLE="width: 500"]
<tbody>[TR]
[TD]
[TABLE="width: 1226"]
<tbody>[TR]
[TD="width: 128, bgcolor: transparent"][/TD]
[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"][/TD]
[TD="bgcolor: transparent"]DEC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]MT
[/TD]
[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"]ND
[/TD]
[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"]MN
[/TD]
[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"]SD
[/TD]
[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"]Total
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]=SUM(C3:C6)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Actually it appears that I spoke too soon. Thanks to your formula I can create an Index/Match function that correctly captures expected value, however when I expand Match to be multi-variables I'm getting #NA . I've independently checked each variable and they will work inside Index/Match, but when I try to expand function, it's not cooperating. I'm new on here, so maybe this should be new thread.

 

[RasGhul]

This match syntax doesn't look right it should be lookup_value,lookup_array,[match type]. [FONT=&quot]MATCH(1,'Sep Corn Stocks'!$F$2:$F$53=Corn!B3).[/FONT] By itself this will always result in #NA .

There's a few problems with this index match, I think a sumproduct or sumif formula would be better suited to your end goal, are you able to get any sample data so we can work it out?

 

[DR RENO]

RasGhul - Apologize for not responding sooner. Was off on holiday yesterday and didn't check email.

I have now solved this issue, so I'm good. Thank you for all of your help