Peo Sjoblom: multi lookup

  • Thread starter Thread starter Drabbacs
  • Start date Start date
D

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(D2:D11))*
(INDEX('Week 1'!$A$1:$P$1400,,3)))}

'Week 1'!$A$1:$P$1400 is the source data.
D2:D11 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





.
Click to expand...
..
 
Back
Top