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

Adjust to suit


"ExcelMonkey" wrote:

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