multi-column language lookup

[moschen]

Hello,

I would like to build a language lookup table to dynamically translate labels in excel sheets. The lookup table looks something like this:

Please Login or Register  to view this content.

In the sheets I would like to use a VBA function to get the values:

Please Login or Register  to view this content.

My question is now, how to do the lookup most efficient. The language code is stored in a named range.

Best regards,
Mo

[royUK]

Why use VBa, VLOKUP should work

[moschen]

It's mostly a gut feeling. I feel more flexible with a function. Another reason is that I might have to change the function later to work with a language "matrix" instead of a table.
Oh, by the way, how does a multi column lookup look like in a VLookup?

[royUK]

I'm not entirely sure what you mean, but I would have a table of translations on a hidden page 7 use VLOOKUP to populate cells using VLOOKup based on the country chosen

[moschen]

Ok, let me give you some more background information. I am currently working on a prototype for excel based reports that support a wide range of languages. I am currently working on two ideas. The first (let's call it language_list) is described in the initial post:

Please Login or Register  to view this content.

The advantage of this is, that I can store not only a translation lable, but also a description or other values per TEXT_CODE.

Another solution would be to have a language_matrix:

Please Login or Register  to view this content.

It uses the language_code as column headers and the text_code to identify rows. Advantage is, that it is much easier for the users to maintain the different translations.

I would then use in the prototype the =getText( text_code) function to get a translation. Depending on the choosen solution I could simply change the VBA code of this function instead of rewriting the formulars in the report itself. So I can basically leave the report untouched and just change the implementation of the function.

The code for the second solution is already implemented, but I am currently trying to find the most efficient way to implement the first solution.

I found a solution (using match and index) here:
http://www.dailydoseofexcel.com/arch...-column-match/
However I have not tried it yet.

Best regards,
Mo.