Match using array of column and row references to match with

[jkfin1]

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

 

[Pete_UK]

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