VBA with Vlookup dynamic

[sverrie]

Hello,

I have in excel a datasheet with data in column J. In Column L:Q are date which should be found with a VLOOKUP.
In Column J is new data. As sample J10 is new data, and this data should be found in L2:Q9. The range of the old data is always in L:Q, but not always in row 2:9 but sometimes also in row 2:15 as sample.
How can I create a VBA formula with this dynamic table range? See picture below
The formula should lookup the new data in J in the old data range L:Q

20663

[mdmackillop]

You can post a sample workbook using Go Advanced / Manage Attachments.

[sverrie]

You can post a sample workbook using Go Advanced / Manage Attachments.

Yes I know, but it's confidential information.

[offthelip]

You can do this by defining a dynamic Named range.
define a named range and in the "refers to definition" put:

=OFFSET(Sheet1!$L$1,0,0,COUNTA(Sheet1!$L:$L),6)

then in the cell for the vlookup put:

=VLOOKUP(J10,testname,4)
( Note: the 4 is just an illustration)


where Testname is the named ranger you have defined

This will then count how many lines in column J have got values in them and will define the range for the vlookup as that number of rows and 6 columns

[sverrie]

Thanks for reply. The range is always from L:Q but the rows are different any time. Also J10 is not always the same, it also can start at J15. how to make this dynamic?

[offthelip]

That is exactly what a dynamic named range will do for you.
Have a look at:
https://support.microsoft.com/en-gb/help/830287/how-to-create-a-dynamic-defined-range-in-an-excel-worksheet