# Index / Match getting results without an exact match Solved

##### Board Regular
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
ABCDE
1DOELOHDIFFDOEMATCHTYPELOHMATCHTYPE
22.761180011.0000
32.761180011.0001
42.7611800#N/A0-1
52.761180011.0010
62.761180011.0011
72.7611800#N/A1-1
82.7611800#N/A-10
92.7611800#N/A-11
102.7611800#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
ABCDE
13DOELOHDIFFDOEMATCHTYPELOHMATCHTYPE
142.9802440#N/A00
153.055965#N/A01
163.065704#N/A0-1
173.0331758#N/A10
183.0271643#N/A11
192.967375#N/A1-1
202.8571218#N/A-10
212.7131411#N/A-11
222.5952029#N/A-1-1
DIFFERENCE

Can anyone tell me what I am doing wrong?

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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?

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?

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.

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?

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.

I get reasonable results with my sample:
Book2
ABCDEFGHIJKLMNO
1DOELOHDIFF02004006008001000120014001600
21.4539921.80112345678
32.1965501.8611011121314151617
42.05704491.9211920212223242526
52.251758811.9812829303132333435
61.8051643182.0413738394041424344
71.737522.1014647484950515253
81.9211218342.1615556575859606162
91.222141182.2216465666768697071
101.9031500262.2817374757677787980
Sheet1

Are any of the DOE values results of formulas?

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!

Replies
9
Views
236
Replies
9
Views
194
Replies
13
Views
307
Replies
3
Views
215
Replies
2
Views
356

1,203,078
Messages
6,053,402
Members
444,662
Latest member
AaronPMH

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back