Returning an array of lookup values from an input array, rather than single value

[jase250]

Hi there,

I have a range of values that will be a non-contiguous subset of the header row of a table of data. I need to retrieve an array of data a known offset from the cells that the range of values match.

I thought the obvious approach was to use HLOOKUP and pass the range in, expecting to have an array of reults returned (CSE):

{=HLOOKUP(Lookup_Values, Table_of_Data, Offset, 0)}

This fails as HLOOKUP will only return the first result, not the complete array. The same happens with VLOOKUP.

My next attempt was to use MATCH, which successfully returns an array of integers, representing the columns that hold the matched headers:

{=MATCH(Lookup_Values, Table_of_Data,0)} = Array_of_columns, eg {2,4,5,7}.

From there I thought it would be trivial to build an array of addresses that I could resolve with INDIRECT:

Address_Array = {"Somesheet!B15", "Somesheet!D15", "Somesheet!E15", "Somesheet!G1"}

{INDIRECT(Address_Array)}

This also fails as INDIRECT only returns the first result.

Any advice on how to solve this, or suggestions for a different approach would be appreciated.

Cheers, Jason.