Peo Sjoblom: multi lookup

[Drabbacs]

Peo,

I cut and pasted our discussion about summing the results
of multiple lookups from the same source range. I tried
the formula you suggested but it didn't work (see below).

Just a quick refresher statement of my problem: I want the
sum of a variable number (between 1 and 10 inclusive) of
lookups from 1 source range.


Thanks for your help.

---earlier discussion---

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

-----Original Message-----
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


--

Regards,

Peo Sjoblom





.

..

 

[Peo Sjoblom]

I can email you a sample if you give a real email address..

 

[drabbacs]

Peo,

I've sent you an email from my work account.

 

[Peo Sjoblom]

Use this address instead since I am at work

(e-mail address removed)