Match using array of column and row references to match with

J

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
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top