Index / Match getting results without an exact match Solved

[adam_neb]

I am using Index / Match to bring back data in a table. When I test using an exact match on both the row and the column match, I get the expected results:

OS&D Reefer Burn Calculator.xls
	A	B	C	D	E
1	DOE	LOH	DIFF	DOEMATCHTYPE	LOHMATCHTYPE
2	2.761	1800	11.00	0	0
3	2.761	1800	11.00	0	1
4	2.761	1800	#N/A	0	-1
5	2.761	1800	11.00	1	0
6	2.761	1800	11.00	1	1
7	2.761	1800	#N/A	1	-1
8	2.761	1800	#N/A	-1	0
9	2.761	1800	#N/A	-1	1
10	2.761	1800	#N/A	-1	-1
DIFFERENCE

.

However, when I use actual data I do not get any resultes no matter which match_type I select:

OS&D Reefer Burn Calculator.xls
	A	B	C	D	E
13	DOE	LOH	DIFF	DOEMATCHTYPE	LOHMATCHTYPE
14	2.980	2440	#N/A	0	0
15	3.055	965	#N/A	0	1
16	3.065	704	#N/A	0	-1
17	3.033	1758	#N/A	1	0
18	3.027	1643	#N/A	1	1
19	2.967	375	#N/A	1	-1
20	2.857	1218	#N/A	-1	0
21	2.713	1411	#N/A	-1	1
22	2.595	2029	#N/A	-1	-1
DIFFERENCE

Can anyone tell me what I am doing wrong?
Adam

 

[NBVC]

Not sure I quite understand what you're getting at, but for starters are your decimal precision levels the same in both column A and in the DOE named range?

 

[barry houdini]

When you use match types 1 or -1 the range must be sorted accordingly.

I'm not sure you're using the most appropriate formula. What range does diff refer to?

 

[adam_neb]

Thanks for the response. DOE is the average cost of fuel to three-decimal points, ($.¢¢¢). LOH is Length of Haul, a whole number, no decimal points (nnn).

I need to match the actual, current DOE average rate with the DOE Range in my table (table range is $1.801 - $4.440 in .06 increments). LOH is Length of Haul from 0 - 2000+ in 200 mile increments (0 - 199, 200 - 399, 400 - 599, etc.).

The DOE EIA Average for week of September 25 is $2.595. If my LOH is 965 I need the value in my table at the intersection of 2.581-2.640 as the DOE and 800-999 as the LOH. However, under this situation my formula returns #N/A.

 

[NBVC]

So why is it you need refer to various match types? If the two lists are sorted in ascending order....then match type of 1 should do it.

By the way the list are entered something like this righ? 0,200, 400, 600, etc....not 0-199,200-399,400-599?

 

[adam_neb]

I may have complicated things a tad...

I do NOT need to refer to various match types. I got my formual to bring back data when I had exact matches in row & column, but when I tried datat that was NOT an exact match it returned #N/A. In the Microsoft Help file it mentions match_type can be -1, 0, 1, etc. So, I used all combinations of -1,0,1 for the row match and the column match to test which match_type would work. Ugh! when there is not an exact match nothing works! After trying all combinations and searching the site, I decided to post...

My list in the columns are 0, 200, 400, etc.
Rows is 1.801, 1.861, 1.921, etc.
Both are in ascending order.

 

[NBVC]

I get reasonable results with my sample:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	DOE	LOH	DIFF				0	200	400	600	800	1000	1200	1400	1600
2	1.45	399	2				1.801	1	2	3	4	5	6	7	8
3	2.1	965	50				1.861	10	11	12	13	14	15	16	17
4	2.05	704	49				1.921	19	20	21	22	23	24	25	26
5	2.25	1758	81				1.981	28	29	30	31	32	33	34	35
6	1.805	1643	18				2.041	37	38	39	40	41	42	43	44
7	1.7	375	2				2.101	46	47	48	49	50	51	52	53
8	1.921	1218	34				2.161	55	56	57	58	59	60	61	62
9	1.222	1411	8				2.221	64	65	66	67	68	69	70	71
10	1.903	1500	26				2.281	73	74	75	76	77	78	79	80
Sheet1

Are any of the DOE values results of formulas?

 

[adam_neb]

OMG! You hit it right on! The table provided is a range. I used =Left(cell,5) which made my named range TEXT. When I changed to a value it worked correctly.

It's the little things that can drive one crazy! Thanks so much for the assist.

=INDEX(DIFF,MATCH(A14,DOE,1),MATCH(B14,LOH,1))

Formula Works!
Adam