# Thread: HLOOKUP (2000SBE)

1. ## 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. ## 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>

3. ## 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,

4. ## 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>

5. ## 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. ## 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.

7. ## 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. ## 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
•