Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LOOKUP a text string created from IF statement | Excel Discussion (Misc queries) | |||
multi sheet lookup with multiple results | Excel Discussion (Misc queries) | |||
lookup problems | New Users to Excel | |||
Lookup problems | Excel Worksheet Functions | |||
Lookup function w/Text and Year | Excel Worksheet Functions |