Index / Match getting results without an exact match Solved

adam_neb

Board Regular
Joined
Jun 9, 2002
Messages
101
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?
Adam
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Board Regular
Joined
Jun 9, 2002
Messages
101
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828

ADVERTISEMENT

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

Board Regular
Joined
Jun 9, 2002
Messages
101
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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?
 

adam_neb

Board Regular
Joined
Jun 9, 2002
Messages
101
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
 

Forum statistics

Threads
1,137,339
Messages
5,680,910
Members
419,940
Latest member
WilliamPorter

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top