Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup from an array
I have an array which has 26 pipe diameter sizes in column A ( from A203 to
A228.) It has 17 pipe thickness across Row 202 ( from B202 to R202). At the intersections of the array are the pre-determined welding hours to suit the particular pipe size / pipe thickness. Toward the top of the spreadsheet, I am using 2 data validation lists in adjacent cells B3 and C3 where I select the pipe size (B3) and select the pipe thickness (C3). I need a formula to enter into cell D3 which will display the resultant welding hours from the array below, based on the size and thickness that are selected at B3 and C3. Any help would be appreciated. GrantW |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup from an array
Are you looking for *exact matches only* of the size and thickness or, if
exact matches don't exist do you want *approximate matches* ? Try this for exact matches only: =VLOOKUP(B3,A202:R228,MATCH(C3,A202:R202,0),0) If you need approximate matches when exact matches don't exist then you'll have to sort your table a certain way depending on the requirements. For example, if an exact match doesn't exist do you want the next larger size or the next smaller size? -- Biff Microsoft Excel MVP "GrantW" wrote in message ... I have an array which has 26 pipe diameter sizes in column A ( from A203 to A228.) It has 17 pipe thickness across Row 202 ( from B202 to R202). At the intersections of the array are the pre-determined welding hours to suit the particular pipe size / pipe thickness. Toward the top of the spreadsheet, I am using 2 data validation lists in adjacent cells B3 and C3 where I select the pipe size (B3) and select the pipe thickness (C3). I need a formula to enter into cell D3 which will display the resultant welding hours from the array below, based on the size and thickness that are selected at B3 and C3. Any help would be appreciated. GrantW |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup from an array
Thanks Valko,
Legendary status for you, brilliant :) "T. Valko" wrote: Are you looking for *exact matches only* of the size and thickness or, if exact matches don't exist do you want *approximate matches* ? Try this for exact matches only: =VLOOKUP(B3,A202:R228,MATCH(C3,A202:R202,0),0) If you need approximate matches when exact matches don't exist then you'll have to sort your table a certain way depending on the requirements. For example, if an exact match doesn't exist do you want the next larger size or the next smaller size? -- Biff Microsoft Excel MVP "GrantW" wrote in message ... I have an array which has 26 pipe diameter sizes in column A ( from A203 to A228.) It has 17 pipe thickness across Row 202 ( from B202 to R202). At the intersections of the array are the pre-determined welding hours to suit the particular pipe size / pipe thickness. Toward the top of the spreadsheet, I am using 2 data validation lists in adjacent cells B3 and C3 where I select the pipe size (B3) and select the pipe thickness (C3). I need a formula to enter into cell D3 which will display the resultant welding hours from the array below, based on the size and thickness that are selected at B3 and C3. Any help would be appreciated. GrantW |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup from an array
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "GrantW" wrote in message ... Thanks Valko, Legendary status for you, brilliant :) "T. Valko" wrote: Are you looking for *exact matches only* of the size and thickness or, if exact matches don't exist do you want *approximate matches* ? Try this for exact matches only: =VLOOKUP(B3,A202:R228,MATCH(C3,A202:R202,0),0) If you need approximate matches when exact matches don't exist then you'll have to sort your table a certain way depending on the requirements. For example, if an exact match doesn't exist do you want the next larger size or the next smaller size? -- Biff Microsoft Excel MVP "GrantW" wrote in message ... I have an array which has 26 pipe diameter sizes in column A ( from A203 to A228.) It has 17 pipe thickness across Row 202 ( from B202 to R202). At the intersections of the array are the pre-determined welding hours to suit the particular pipe size / pipe thickness. Toward the top of the spreadsheet, I am using 2 data validation lists in adjacent cells B3 and C3 where I select the pipe size (B3) and select the pipe thickness (C3). I need a formula to enter into cell D3 which will display the resultant welding hours from the array below, based on the size and thickness that are selected at B3 and C3. Any help would be appreciated. GrantW |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup array | Excel Worksheet Functions | |||
Lookup an array | Excel Discussion (Misc queries) | |||
Lookup "greater than or equal to" in lookup array | New Users to Excel | |||
IF ARRAY LOOKUP ??? Help Please | Excel Worksheet Functions | |||
How do I use <= in a lookup array? | Excel Discussion (Misc queries) |