Calculate last Empty Row and use that in Vlookup?

[shellp]

Hello

I am working with a table of may rows of data that could change in future. I don't want to create a named range for the table because it will slow it down. I am calculating the last row by CountIf and putting that in the named range of End_1. I want to use the result in the vlookup but can't get it to work i.e.
vlookup(B4,Data_Worksheet!$A$2:$DM$" & End_1 & ",7, False).

Thanks.

 

[Indystick]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]a[/TD]
[TD]1[/TD]
[TD]Lookup Value[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]b[/TD]
[TD]2[/TD]
[TD]Result:[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]c[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]d[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I tried to solve your problem using the table above. Here is the solution I came up with:

=VLOOKUP(E1:E1,OFFSET(A1,0,0,COUNTA(A:A),2),2,FALSE)

I tested it, and it continues to work as I ad rows. The offset dynamically assigns the height of the table array using COUNTA to count the number of non-empty cells in the column (you could do something similar to dynamically assign the width of the table array as well).

You would need to adjust cell references to your particular layout of course. Also: this method precludes you from having any data other than your table data in the column you do the COUNTA on.

 

[JoeMo]

Try this:

vlookup(B4,Data_Worksheet!&INDIRECT("$A$2:$DM$"&End_1),7, False)

 

[shellp]

Thanks Joe...but it isn't working. I get an error message "the formula you typed has an error" and it highlights the word INDIRECT. Other thoughts?

 

[shellp]

Hi Indystick

Thanks for responding but I'm not sure what this formula does i.e. it doesn't indicate the height and width of the table. Please explain?

 

[JoeMo]

Thanks Joe...but it isn't working. I get an error message "the formula you typed has an error" and it highlights the word INDIRECT. Other thoughts?

What's the value of the named range End_1 when you get the error? it must be an integer. You could try:

vlookup(B4,INDIRECT("Data_Worksheet!$A$2:$DM$"&End_1),7, False)

 

[shellp]

HI Joe

Should the data type be text or integer? The field of End_1 is based on a cell formula =Countif(Data_Worksheet!D:D5021,">0"). So should I make this a text field? Thanks.

 

[shellp]

Joe, I changed End_1 to be a text field and then did as per your second example i.e. including the entire worksheet name etc. between the parentheses of the Indirect function and it worked. Thanks so much!

 

[JoeMo]

Joe, I changed End_1 to be a text field and then did as per your second example i.e. including the entire worksheet name etc. between the parentheses of the Indirect function and it worked. Thanks so much!

You are welcome - thanks for the reply.