Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
match in multi-column and multi-row array | Excel Discussion (Misc queries) | |||
Array Formula w/ COLUMN & MATCH FUNCTIONS | Excel Discussion (Misc queries) | |||
Match as well as does not match array function | Excel Discussion (Misc queries) | |||
Any way for 2 column vlookups. i.e match last name then match firs | Excel Worksheet Functions |