Both of these answers work in terms of providing me with the correct
sumproduct answer. But its not exactly what I am looking for. My goal is to
create a weighted average calc. I will do so by doing the following:

SUMPRODUCT(Range1,Range2)/Total of Range1

The simple example I provided was looking to populate the first part of the
SUMPRODUCT with (Range1):
{100,105,110,115, 120}

If this worked I would then do the same for Range2 (Assuming I had a range
of values in L15:P15 that read as follows:

Profile 1 Profile 2 Profile 3 Profile 4 Profile 5

I would then get the second part of the formula:
{0.50,0.68,0.51,0.50, 0.87}

The result would be:

SUMPRODUCT({100,105,110,115, 120},{0.50,0.68,0.51,0.50, 0.87})/Total

If this sounds confusing I can send a spreadsheet example and show you what
I am trying to do. May be a little more clear if you see it.

Thanks

EM





"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(ISNUMBER(MATCH(L4:L13,L16:P16,0))),M4:M13)

Biff

"T. Valko" wrote in message
...
ooops!

Note the reason I am doing this is that I putting the Lookup into a
SUMPRODUCT formula.

Disregard my reply!

Biff

"T. Valko" wrote in message
...
Try this in L18:

=VLOOKUP(L16,$L4:$M13,2,0)

Copy across as needed.

Biff

"ExcelMonkey" wrote in message
...
I am using a LOOKUP and do not understand what is going on. In cell L18
I am
using the following formula:

=LOOKUP($L16:$O16,INDEX($L$4:M$13,0,0))

When I highlight the formula and hit F9 I get:

{100,105,110,115}

Which is what I want. However if I increase the range in the formula to
column P:

=LOOKUP($L16:$P16,INDEX($L$4:M$13,0,0))

When I highlight and hit F9 I get:

{100,105,110,115,0.50}

Why does this not work? The last value should be 120. The goal here
being
that I want to use a lookup variable which is a range and search for it
in
another range and return a particual column of adjacent values.

Note the reason I am doing this is that I putting the Lookup into a
SUMPRODUCT formula.



L M N O P
Q
1
2
3
4 Profile 1 0.50
5 Profile 2 0.68
6 Profile 3 0.51
7 Profile 4 0.50
8 Profile 5 0.87
9 Profile 6 100
10 Profile 7 105
11 Profile 8 110
12 Profile 9 115
13 Profile 10 120
14
15
16 Profile 6 Profile 7 Profile 8 Profile 9 Profile 10
17
18 ?

Thanks

EM