Vlookup with Hlookup

G

Guest

Guest
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.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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 ?
 
Upvote 0
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.
 
Upvote 0
On 2002-03-14 09:48, Steve Hartman wrote:
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 ?
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.
 
Upvote 0
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,How do I write a Lookup formula that will return the weight per foot if I enter the pipe diameter and quantity, 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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top