If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
HLookup
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? . |
#3
|
|||
|
|||
HLookup
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? |
#4
|
|||
|
|||
HLookup
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? . |
#5
|
|||
|
|||
HLookup
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? . |
Thread Tools | |
Display Modes | |
|
|