Problems with LOOKUP

[ExcelMonkey]

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

 

[Teethless mama]

=SUMPRODUCT((ISNUMBER(SEARCH("Profile "&{6,7,8,9,10},E4:E13)))*(F4:F13))

Adjust to suit

 

[T. Valko]

Try this in L18:

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

Copy across as needed.

Biff

 

[T. Valko]

ooops!

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

Disregard my reply!

Biff

 

[T. Valko]

Try this:

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

Biff

 

[ExcelMonkey]

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 L1515 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]

Your setup isn't conducive for this task!

It should ideally be setup like this:

100...0.50
105...0.68
110...0.51
115...0.50
120...0.87

then:

=SUMPRODUCT(M9:M13,N9:N13)/SUM(M9:M13)

I don't have time to try and figure it out based on your setup. Maybe
someone will "drop" by in the meantime, if not, I'll take another look
tomorrow.

Biff

 

[T. Valko]

Try this:

=SUMPRODUCT(OFFSET(L4,MATCH(L15,L4:L13,0)-1,1,5),OFFSET(L4,MATCH(L16,L4:L13,0)-1,1,5))/SUM(OFFSET(L4,MATCH(L16,L4:L13,0)-1,1,5))

Biff