HLookup

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

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)
 
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),COUNTA($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
 
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.
 
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
 
Back
Top