Vlookup with Hlookup

I have a table of pipe sizes. The left column lists the pipe diameter. The first row list the pipe schedule (wall thickness). The body of the table lists the weight per foot for each pipe diameter/wall thickness combination. My estimating spread sheet (material takeoff) lists pipe quantities and pipe sizes. How do I write a Lookup formula that will return the weight per foot if I enter the pipe diameter and quantity. I think I need a combination Vlookup/Hlookup formula but I can't make mine return the value.

[Steve Hartman]

On 2002-03-14 09:42, Anonymous wrote:
I have a table of pipe sizes. The left column lists the pipe diameter. The first row list the pipe schedule (wall thickness). The body of the table lists the weight per foot for each pipe diameter/wall thickness combination. My estimating spread sheet (material takeoff) lists pipe quantities and pipe sizes. How do I write a Lookup formula that will return the weight per foot if I enter the pipe diameter and quantity. I think I need a combination Vlookup/Hlookup formula but I can't make mine return the value.

Do you mean return the total weight, since the weight per foot doesn't change with quantity ?

[Mark W.]

On 2002-03-14 09:42, Anonymous wrote:
I have a table of pipe sizes. The left column lists the pipe diameter. The first row list the pipe schedule (wall thickness). The body of the table lists the weight per foot for each pipe diameter/wall thickness combination. My estimating spread sheet (material takeoff) lists pipe quantities and pipe sizes. How do I write a Lookup formula that will return the weight per foot if I enter the pipe diameter and quantity. I think I need a combination Vlookup/Hlookup formula but I can't make mine return the value.

Use a combination of INDEX and MATCH. MATCH behaves much like VLOOKUP but returns a row/column number (depending on the orientation of the lookup array) which can then be used with INDEX. Have a look at the Help topics for these worksheet functions.

[kcdill]

On 2002-03-14 09:48, Steve Hartman wrote:

Do you mean return the total weight, since the weight per foot doesn't change with quantity ?

Steve: Thanks for the response. The formula will return the unit weight per foot. I use other formulae to multiply total length per unit weight.

[Aladin Akyurek]

On 2002-03-14 09:42, Anonymous wrote:
I have a table of pipe sizes. The left column lists the pipe diameter. The first row list the pipe schedule (wall thickness). The body of the table lists the weight per foot for each pipe diameter/wall thickness combination. My estimating spread sheet (material takeoff) lists pipe quantities and pipe sizes. How do I write a Lookup formula that will return the weight per foot if I enter the pipe diameter and quantity. I think I need a combination Vlookup/Hlookup formula but I can't make mine return the value.

Kent,

Given the essential question,[i]How do I write a Lookup formula that will return the weight per foot if I enter the pipe diameter and quantity,[i] and off-board privileged knowledge of the layout of your lookup table, what follows will retrieve the desired values.

[1] This is a portion of the top row of your lookup table:

{"","5s ",5,"10s ",10,20,30,"Std ",40,60,"XS"}

[2] This is a portion of the left-most column of your lookup table:

{"0.125""Ø";"0.25""Ø";"0.5""Ø";"0.625""Ø";"0.75""Ø";"1""Ø";"1.25""Ø";"1.5""Ø";"2""Ø";"2.5""Ø";"3""Ø"}

Moved to a sheet of its own: the top row occupies B2:R2 and left-most column values occupy A3:A35.

Assign a name to A2, e.g., Base, via the Name Box on the Formula Bar.

Assign a name to B2:R2, e.g., Fields, which are apparently a set of different wall thickness values.

Assign a name to A3:A35, e.g., SchedVals, which apparently represent a set of different pipe sizes.

The lookup formula that you are looking for becomes, given a pipe size and a wall thickness:

=IF(C20>0,OFFSET(Base,MATCH(D20,SchedVals,0),MATCH(E20,Fields,0)),"")

where C20 houses a quantity, D20 a pipe size, and E20 a value for wall thickness.

Aladin