Match using array of column and row references to match with
 

Can you use the match function or any combination of any function to find in
intersecting cell's value by matching to arrays of row values or matching to
a range of values per column. For instance, see data array and row and column
figures below. The row range is gas price range based on a certain date and
the column is miles hauled. Need to find the intersection to come up with
haul rate.

Miles Hualed
1 to 5 6 to 10 11 to 15
3.00 3.09 $0.170 $0.190 $0.209
3.10 3.19 $0.172 $0.192 $0.211
3.20 3.29 $0.174 $0.194 $0.213
3.30 3.39 $0.176 $0.196 $0.215















--
Kelly

Match using array of column and row references to match with
 

Instead of "1 to 5" and "6 to 10" etc, just put the start values in
those cells (assumed to be C2, D2, E2, containing 1, 6 and 11
respectively). Then you can use this approach:

=INDEX(C3:E6,MATCH(G1,A3:A6),MATCH(H1,C2:E2))

where G1 is for the Gas price and H1 is for the miles hauled.

Hope this helps.

Pete

On Sep 16, 4:25*pm, jkfin1 wrote:
Can you use the match function or any combination of any function to find in
intersecting cell's value by matching to arrays of row values or matching to
a range of values per column. For instance, see data array and row and column
figures below. The row range is gas price range based on a certain date and
the column is miles hauled. Need to find the intersection to come up with
haul rate.

* * * * * * * * Miles Hualed * * * * * *
* * * * * * * * 1 to 5 * 6 to 10 * * * * 11 to 15
3.00 * *3.09 * * $0.170 * * * * *$0.190 * * * * *$0.209
3.10 * *3.19 * * $0.172 * * * * *$0.192 * * * * *$0.211
3.20 * *3.29 * * $0.174 * * * * *$0.194 * * * * *$0.213
3.30 * *3.39 * * $0.176 * * * * *$0.196 * * * * *$0.215

--
Kelly