Array formula: Automatically create a series of values for a one-dimensional matrix

[bbozze]

Hi,

New to this forum, and I have a question that I haven't found any answer for yet.

The question is only about optimizing Excel code, really.

I have created an array formula with VLOOKUP that works fine. However, I have a string of consecutive values that I had to type myself (the values are used as column index for the VLOOKUP formula). See formula below:

{=VLOOKUP(D2;'Database'!A:AL;_11_to_38;FALSE)}

Where "_11_to_38" refers to the string:
{11\12\13\14\15\16\17\18\19\20\21\22\23\24\25\26\27\28\29\30\31\32\33\34\35\36\37\38}

As I said, the formula works fine. But my question is if this string could be created in a more optimized way?
I have tried the functions ROW(11:38) and COLUMN(11:38). They produce values when used by themselves in a sheet, but don't work when used in the array formula above.

Beware, I use a Swedish Excel, so some of the above functions and formulas could be misspelled or have syntax errors due to translation differences.

Thanks in advance!

/Andreas

[hemesh]

try using
Row(indirect("11:48") or transpose(Row(indirect("11:48"))
to generate series inside an array formula

[Kaper]

As you are trying VLOOKPUP (not HLOOKUP) the array shall be horizontal.
Probably you were pretty close with COLUMNS(11:38) but it is not allowed formula. Try:

Please Login or Register  to view this content.

(11th column is K, while 38th AL)

or move advanced with columns numbers (not letters):

COLUMN(INDIRECT(ADDRESS(1,11)&":"&ADDRESS(1,38)))

[bbozze]

Of course, columns are named by letters and not numbers, stupid of me

Thank you, both suggestions works like a charm! And the transpose version makes it a little more simple to count manually