Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    HLOOKUP (2000SBE)

    Hi all,

    I am trying to put together a seemingly complex HLOOKUP function. The formula I have is:

    =HLOOKUP($C$1,SHEET1,2)

    Works great if I hard code the row number into each formula.

    If this formula were in cell C2, I would like to change the formula to lookup the matching value in A2, and then return the row number for the matching value in the named range. Is this possible?

    I have tried this to no avail, because it returns the row number for the value in C2, which is not necessarily the same row number in the named range:
    =HLOOKUP($C$1,SHEET1,Row("C2"))

    I guess I am trying to lookup in the leftmost column of the named range, the value which matches the value in A2, then return the row number of the value which matches the value in A2, to the HLOOKUP function.

    Thanks,

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HLOOKUP (2000SBE)

    I'm not real clear on what you are trying to do. However, I think this formula may be a little closer:

    <pre>=HLOOKUP($C$1,SHEET1!$A$1:$Z$100,Row(C2))
    </pre>

    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HLOOKUP (2000SBE)

    I am attaching a sample cut with two hlookup functions in it. First one acheives the desired result through hardcoding the row number of the range. Second tries to achieve the same result without putting the actual row number in.

    Thanks,
    Attached Files Attached Files

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HLOOKUP (2000SBE)

    Now I'm confused about what you are trying to do. This formula will do what your formula with the fixed row number is doing:

    <pre>=HLOOKUP($A$1,TestName,ROW(A7)-1)
    </pre>


    However, now I am confused about what the lookup of the word "the" has to do with it. Are you trying to do a two dimensional lookup of a date in the top row and a word in column one and return the value at the intersection of that row and column? If that is what you want to do, then I think you will need a combination of the Match and Offset functions. Something like this:

    <pre>=OFFSET(Sheet1!$A$1,MATCH($A$3,Sheet1!$A$2:$A $9,0),MATCH($A$1,Sheet1!$A$1:$F$1,0)-1)
    </pre>

    Legare Coleman

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HLOOKUP (2000SBE)

    You characterized exactly what I was trying to do with the OFFSET function. Can you use the named range in the MATCH function, as opposed to specifying the actual range? I tried it and it did not seem to work using the named range.

    Thanks,

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HLOOKUP (2000SBE)

    There should be no problem using a named range with Match. However, you will need two named ranges, one for the top row and a second for the left column. Match works on a one dimensional array. If you give it a multi-dimensional array it does not know which direction to search.
    Legare Coleman

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    St. George, Maine, USA
    Posts
    158
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HLOOKUP (2000SBE)

    Try this in C7 of your sample:

    =HLOOKUP($A$1,TestName,MATCH(A7,Sheet1!A1:A9,0))

    The above 'Sheet1!A1:A9' can be replaced with a named range.
    The 3rd argument of the MATCH function is '0' as the search column is not sorted.

    Good luck,
    Bob_D

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HLOOKUP (2000SBE)

    Beautiful! Works like a charm! Many thanks!

Posting Permissions

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