Lookup with a couple of variables

[J]

I have a table of cost per pound based on weight and miles.

Column A is the miles (0 - 100, 101 - 250, 251 -500,..)
Weight across (0-300, 301 -450,...)

I need to find the cost per pound in the table based on these two
variables.

I've tried:
=INDEX($E$5:$H$7,MATCH(A18,D47,0),MATCH(B18,F4:H4,0))

but that only gives me the exact matches. I'm not sure how to find the
match within the ranges.

The whole thing would look like this:

miles pu weight
0 300 301 400 401 500
0 100 50 .1 .45 .55
101 200 100 .2 .35 .65
201 250 150 .3 .95 .75

So if I had 350lbs and went 120 miles, I need to get .35 and pu of
100.

Also, there will be three or four of these tables and each employee
has a table assigned to them. Suppose that we need to do a batch thing
at the end of the month. Ideally Crystal Reports could export to excel
and stick the weights, miles, and employee name on a spreadsheet. Then
excel displays the values. Right now, we have someone looking up all
of these figures then looking in the table, then filling out the
spreadseet. Any ideas?

Thanks

 

[Frank Kabel]

Hi
only use the lower boundary of your values and use MATCH with '1' as
the last parameter

 

[Domenic]

Hi,

With regards to the first part of your post, using your table as an
example, and assuming that the ranges for weight and miles are separated
by a hyphen and each within one cell (0-300, 301-400, etc., and 0-100,
101-200, etc.), try the following array formulas which need to be
entered using CTRL+SHIFT+ENTER:

=INDEX(C2:E4,MATCH(B15,--LEFT(A2:A4,FIND("-",A2:A4)-1),1),MATCH(A15,--LEF
T(C1:E1,FIND("-",C1:E1)-1),1))

=INDEX(B2:B4,MATCH(B15,--LEFT(A2:A4,FIND("-",A2:A4)-1),1))

where A15 contains your criteria for weight and B15 your criteria for
miles.

Hope this helps!