multiple lookup.. N/A

[Drabbacs]

I have a workbook composed of 13 source sheets and a
target/summary sheet. The summary sheet pulls info, using
vlookups from the 13 source sheets based on a single input
cell. This works well.

Now I've been asked to provide a functionality that will
support summing info for 10 lookups simultaneously.

I started with a simple vlookup(1) + vlookup(2) +..+
vlookup(10). However, if someone only wants to lookup 3
things, the formula returns N/A.

Then I started to enclose the vlookups in a if(isna(vlookup
(1),0,vlookup(1)))+if(isna(vlookup(2),0,vlookup(2)))+...
structure. That made the formula too long to be handled.

So my question is, how can I get a sum of *upto* 10
distinct lookups while avoiding N/A problems?

Thanks in advance for any help.

Drabbacs

 

[Peo Sjoblom]

One way

=SUMPRODUCT(SUMIF(INDIRECT("'"&MySheets&"'!A2:A50"),A1,INDIRECT("'"&MySheets
&"'!B2:B50")))

put a list of all your sheet names in a range somewhere off in the summary
sheet, select the range and insert>name>define
and give it a name like MySheets, then use the above formula where A1 is the
lookup value in A and the summing are done in B Won't work of course if the
tables are not of equal size and in equal place. Then you could put all 13
vlookup
formulas in one cell each including the error trapping then sum that range
with a regular sum..

 

[Drabbacs]

Peo,

I'm sorry I think I mislead you in my phrasing. I don't
need the sum of the lookups from 13 sheets. I need the sum
of 10 possible lookups from one sheet.

To put it another way I want the sum of a variable number
(1 to 10) of lookups from 1 source range.


Drabbacs

 

[Peo Sjoblom]

One source range is the lookup table? Select the table, do
insert>name>define
and name it MyTable, assume your 10 lookups are in A1:A10, also assume you
want to return the values from the second column of the vlookup table

Then use this array formula

=SUM((INDEX(MyTable,,1)=TRANSPOSE(A1:A10))*(INDEX(MyTable,,2)))

entered with ctrl + shift & enter

the last 2 in the formula is from which column in the vlookup table you want
the values
to be summed. If your columns may vary then I'd suggest you create a range
somewhere off view,
name it something and put the 10 lookups there with error trapping, then sum
that range
The formula I gave you will lookup the values in A1:A10 in the first column
(leftmost) from
the vlookup table called MyTable and return the values from the column 2

 

[Drabbacs]

Either I misunderstood you, or there is an error in that
formula. Is there supposed to be '=' before the
TRANSPOSE ? I get a #value? result.

{=SUM((INDEX('Week 1'!$A$1:$P$1400,,1)=TRANSPOSE(D211))*
(INDEX('Week 1'!$A$1:$P$1400,,3)))}

'Week 1'!$A$1:$P$1400 is the source data.
D211 is the 10 cells to look up.
A sample lookup: HHGB000023P9765

should return a number from column 3

Drabbacs