HLookup

I am trying to build a template and one of the formulas
that I have to use is HLookup. Is there a way to write
this formula if I don't know what the last row is? In
VBA, I have found ways to find the last row, but is there
a way to find the last row using a formula?

If after you do your lookup your cursor will be at the
position you want to be your lastrow. You could then use:
dim rownum as integer

rownum = ActiveCell.Row
Then use:
Rows("1:" & rownum).Select
to select your rows

or if you want to find a match (say find "2" in column N)
to be your lastrow you could use:

lastrow = Application.Match(2, Range("n:n"), 0)

-----Original Message-----
I am trying to build a template and one of the formulas
that I have to use is HLookup. Is there a way to write
this formula if I don't know what the last row is? In
VBA, I have found ways to find the last row, but is there
a way to find the last row using a formula?

.

So, you want to use hlookup, lookup a value in the first row, then you would
go to the last row with something in it to retrieve the value? If that is so
you can use

=HLOOKUP(A8,OFFSET($G$3,,,COUNTA($G$3:$G$1000),COU NTA($G$3:$IV$3)),COUNTA($G
$3:$G$1000),0)

where a normal hlookup would look like

=HLOOKUP(A8,G3:J14,12,FALSE)

so replace G3 with the first upper leftmost cell in the table and A8 with
the lookup value cell

--

Regards,

Peo Sjoblom


"Steve" wrote in message
...
I am trying to build a template and one of the formulas
that I have to use is HLookup. Is there a way to write
this formula if I don't know what the last row is? In
VBA, I have found ways to find the last row, but is there
a way to find the last row using a formula?

Did not work for me. here is the formula that I am
currently using.

=HLOOKUP(B3,Sheet1!$1:$65536,102,0)

I would like to modify this so that 102 would be the last
row that there is data in. That way I can create a
template where the last row may vary.




-----Original Message-----
So, you want to use hlookup, lookup a value in the first
row, then you would
go to the last row with something in it to retrieve the
value? If that is so
you can use

=HLOOKUP(A8,OFFSET($G$3,,,COUNTA($G$3:$G$1000),CO UNTA
($G$3:$IV$3)),COUNTA($G
$3:$G$1000),0)

where a normal hlookup would look like

=HLOOKUP(A8,G3:J14,12,FALSE)

so replace G3 with the first upper leftmost cell in the
table and A8 with
the lookup value cell

--

Regards,

Peo Sjoblom


"Steve" wrote in
message
...
I am trying to build a template and one of the formulas
that I have to use is HLookup. Is there a way to write
this formula if I don't know what the last row is? In
VBA, I have found ways to find the last row, but is
there
a way to find the last row using a formula?



.

So you want the table to size with what you put in the row index number?
put the row number in a cell like in C3 and use

=HLOOKUP(B3,OFFSET(Sheet1!$A$1,,,$C$3,256),$C$3,0)

works for me, I did a quick test and if I put 102 in C3 it will lookup the
value from B3
in row1 of sheet1, go down 102 rows to return a value from row 102

--

Regards,

Peo Sjoblom

"Steve" wrote in message
...
Did not work for me. here is the formula that I am
currently using.

=HLOOKUP(B3,Sheet1!$1:$65536,102,0)

I would like to modify this so that 102 would be the last
row that there is data in. That way I can create a
template where the last row may vary.




-----Original Message-----
So, you want to use hlookup, lookup a value in the first
row, then you would
go to the last row with something in it to retrieve the
value? If that is so
you can use

=HLOOKUP(A8,OFFSET($G$3,,,COUNTA($G$3:$G$1000),CO UNTA
($G$3:$IV$3)),COUNTA($G
$3:$G$1000),0)

where a normal hlookup would look like

=HLOOKUP(A8,G3:J14,12,FALSE)

so replace G3 with the first upper leftmost cell in the
table and A8 with
the lookup value cell

--

Regards,

Peo Sjoblom


"Steve" wrote in
message
...
I am trying to build a template and one of the formulas
that I have to use is HLookup. Is there a way to write
this formula if I don't know what the last row is? In
VBA, I have found ways to find the last row, but is
there
a way to find the last row using a formula?



.