Dynamic range for VLookup

[phalcon45]

Im looking for the syntax to set the formula in a cell to a VLookup based on the total count of rows in the lookup table. For example "VLookup(A1,Sheet1!B1:K500,5,False" where K500 might change from 500 to some other number depending on how many rows are in Sheet1 with data in them.

If memory serves me I've done this in the past using a DIM that I set to the CountA value earlier in the script but I cant remember how I did it...

Any thoughts might help me remember how I did it before (years ago)

Thanks!

 

[jeffreybrown]

using a DIM that I set to the CountA value earlier in the script but I cant remember how I did it...

I'm not sure if you are talking about VBA code here or in a worksheet formula.

For a worksheet formula you will need to create a dynamic named ranges and for code you will have to find the last value and this site should be able to answer both for you.

http://www.xldynamic.com/source/xld.LastValue.html#last_any

 

[phalcon45]

I'm not sure if you are talking about VBA code here or in a worksheet formula.

For a worksheet formula you will need to create a dynamic named ranges and for code you will have to find the last value and this site should be able to answer both for you.

http://www.xldynamic.com/source/xld.LastValue.html#last_any

I probably didnt explain what Im trying to do very well. I need the cell in the spreadsheet to have the formula VLookup(A1,Sheet1!B1:K500,5,False) but I need the VB Script to adjust the K500 part to whatever the actual table range is (its a list of employee names that grows and shrinks). So it may need to be VLookup(A1,Sheet1!B1:K503,5,False) or VLookup(A1,Sheet1!B1:K610,5,False) etc.

 

[jeffreybrown]

I think you explained yourself ok, except for the part where you tell us, are you using a straight worksheet formula or VBA?

I read you are working with a worksheet formula.

1) Create a named range

• Ctrl + F3 (Opens Name Manager)
• New
• Name: MyRange
• Refers to: =OFFSET(Sheet1!$B$1,,,COUNTA(Sheet1!$B:$B),10)
• OK

2) Apply the named range to your formula

• =VLOOKUP(A1,MyRange,5,0)

This named range will adjust based off of the length of column B. I understand you said column K, but your table starts with column B; therefore, column B would be the most logical column to create the length. The width is to column K, 10 column from column B.