Vlookup with Hlookup
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Vlookup with Hlookup

  1. #1
    Guest

    Default

    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.

  2. #2
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ?

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Lake Stevens, Washington
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,803
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com