Automatically updating vlookuptable Range, Table in another workbook

[FH1987]

Below is a "vlookup" formula I wrote with the lookup-table located in another workbook.

=VLOOKUP(A7;'[Lookup Table med Formel.xlsm]LookupTable'!$A$2:$B$686;2)

It works fine, however I want the Lookup-table range (the "$A$2:$B$686" part in the formula) to automatically expand when posts are added to the table. Does anyone have a suggestion as to how to write the formula in order to accomplish this?

I have tried
=VLOOKUP(A7;OFFSET('[Lookup Table med Formel.xlsm]LookupTable'!$A$2;0;0;COUNTA(VLookupTable!$A:$A);2);2)

I'm pretty sure the correct formula will be more or less what I've wrote above, but I've had all maner of problems making it work, either it says that I use to many arguments for the function, or it returns the wrong value from the lookup-table, or I get an annoying request to update something or another.

I would highly appreciate it if someone could help me out with this!

PS, note that in the formula I use ";" instead of the normal ",". This is intentional and is the excel setting I use.

Thank you in advance!

 

[Sandeep Warrier]

Hi

In your lookup table workbook, define a named range.

Press Alt+I+N+D to get the names manager on, press the "New" button.
In the "Name:" section, type in a name (without any spaces), for example LookupTable. Ensure that the scope is Workbook. In RefersTo: use the formula

=INDEX(Sheet1!$A:$A,2,0):INDEX(Sheet1!$B:$B,MATCH(9.9999E+307,Sheet1!$B:$B),0)

Change Sheet1 to the name of the sheet containing the lookup table. This formula assumes that the data is in columns A and B, and starts from A2.

If column B contains text entries, use REPT("z",255) instead of 9.9999E+307

In your other workbook, use the VLOOKUP formula as below:

=VLOOKUP(A2,'1234.xlsx'!LookupTable,2,FALSE)

change the part in red to your lookuptable workbook name, and the name of the named range.

Note that in this case, the workbook containing the lookup table needs to be open, otherwise you will get a reference error.

 

[JD_GFI]

If the lookup table range on the 'Lookup Table med Formel.xlsm' sheet is the only data on that particular sheet then the formula

=OFFSET('[Lookup Table med Formel.xlsm]LookupTable'!$A:$A,0,0,COUNTA('[Lookup Table med Formel.xlsm]LookupTable'!$A:$A),COUNTA('[Lookup Table med Formel.xlsm]LookupTable'!$1:$1))

could be used to define a name for the lookup I think and would automatically expand as data was added to either rows or columns.

 

[FH1987]

Thank you for the speedy reply!

I ended up using a simpler and more primitive solution, I just specified the range to a larger number than I expect the Lookup-table will ever reach!

I find amazing how quick the members of this board are to provide detailed suggestions for how to solve various excel issues, I highly appreciate your efforts!