ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Problems with LOOKUP (https://www.excelbanter.com/excel-worksheet-functions/125467-problems-lookup.html)

ExcelMonkey
Problems with LOOKUP
 
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
Problems with LOOKUP
 
=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

T. Valko
Problems with LOOKUP
 
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



T. Valko
Problems with LOOKUP
 
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

T. Valko
Problems with LOOKUP
 
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

ExcelMonkey
Problems with LOOKUP
 
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

T. Valko
Problems with LOOKUP
 
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

"ExcelMonkey" wrote in message
...
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

T. Valko
Problems with LOOKUP
 
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

"T. Valko" wrote in message
...
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

"ExcelMonkey" wrote in message
...
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


All times are GMT +1. The time now is 02:37 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com