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

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.

=INDEX(some_data,MATCH(Lookup_Values, Table_of_Data,0))
array entered should do it I reckon. a sample workbook would have made it clearer though so I could be wrong.

index should work unless one of us missed something in your post :-)

Possibly a moot point if SUMIF is OK for you but actually I think that OFFSET formula was unnecessarily complex, this one should work

=OFFSET(K13:T13,0,MATCH(Lookup_Items,Lookup_Row,0)-1,1,1)

.......but it actually returns an array of references, so, as you say, if you wrap that with a COUNTA function you'll get 1......you need another step to "de-reference" the references, for numeric data you can use N function [for text it would be T] so that becomes

=N(OFFSET(K13:T13,0,MATCH(Lookup_Items,Lookup_Row,0)-1,1,1))

If you wrap that in COUNTA you should get 4 as expected (with numeric values in the table)

LOOKUP allows you to input a range as the lookup value and return an array, e.g.

=LOOKUP(Lookup_Items,Lookup_Row,K13:T13)

.....but for LOOKUP to work Lookup_Row must be sorted ascending.

...alternatively for returning numeric results try SUMIF

=SUMIF(Lookup_Row,Lookup_Items,K13:T13)

No sorting required

Note: for either formula you can also derive K13:T13 using INDEX/MATCH based on B3 if you want e.g. something like =INDEX(K4:T18,MATCH(B3,J4:J18,0),0)

if i understand your requirement correctly (and if i do not, my apologies):

if you weren't able to get a proper result out of the INDEX function, it is due to the fact that your named_ranges were slightly askew. in lookup_table, cell J3 was included, but in lookup_row, it was not.

see attached:

and, in continuation, when you talk of getting the full array ({2,4,6,7}) in the results, do you mean that you would like to get the results corresponding to Value_10 for Item2, Item4, Item6 and Item7? if yes, then you would have to use SMALL function to induce the INDEX function to give you one result at a time over adjacent cells upon the formula being dragged over a few (adjacent) columns (or rows)... it is not possible to get all those values within the same cell.

My 2 cents added:

you can't return an array (visibly) to one cell so what is it you actually want to do with it? I reckon daddylonglegs' answer is probably what you want.

This formula

=SUMIF(Lookup_Row,Lookup_Items,K13:T13)

returns this array {1;1;0;0}

......so it will give the required result, I think, as long as you don't have duplicates in Lookup_Row......and as long as the results to be returned are numeric

basically select 4 cells across, type
=INDEX($K$4:$T$18,MATCH(B3,$J$4:$J$18,0),MATCH($A$3:$A$6,$K$3:$T$3,0))
and Ctrl+shift+enter
in your sample you used value_10 as the lookup rather than value10 though. ;-)

I reckon it would help to know the final required outcome instead then. or you can just array-enter the index formula into the 4 cells and refer to that range in your other functions (easier to audit too)

Did you try SUMIF? If not then this formula would also work, even for non-numeric values

=OFFSET(K13:T13,0,SMALL(MATCH(Lookup_Items,Lookup_Row,0)-1,ROW(INDIRECT("1:"&COUNT(MATCH(Lookup_Items,Lookup_Row,0))))),1,1)