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

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

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

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