Using multiple names and referring to names via cells; when tranferring btw 2 sheets

Hi all,

My Situation: I need to transfer a very large amount of data between two sheets.
In the original sheet, the data is sorted into blocks of data, where each part is named (e.g. Netherlands_Vehicles). And thus not in a neat database structure.

When tranferring the data to the new sheet, which is a better and traditional "2-D" database", I planned to create an extra column with the reference to the names from the other sheet, so that that given row knows which naming range of data to take the data from.

So, for instance, in column B of the new "simple DB" (where the data is GOING TO) , each entry has a reference to the applicable excel defined Name (again, e.g. "Netherlands_Vehicles").

When using the VLOOKUP function to pull the data from the original sheet to the new database, I am not able to use the name from column B directly into the VLOOKUP function simply by using the formula =VLOOKUP(A1;B1;50;false), where B1 is where I had written "Netherlands".

So I will have to adjust all the entries according to the name manually (in this case I have more than 50 names), which is extremely time consuming.

Is there any way I can refer to a name by writing that given name in a cell for that row?

Thank you
Soeren P.

Thanks Teylyn. This was great help, and it particularly helped me better understand the Indirect function, which is useful.