| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
index/match/lookup???
Here is my dilema . . .
On Wksht1 I have the following data Prod Code PriceLevel Price Prod1 1 Prod2 3 Prod3 2 Prod4 3 Prod5 1 In Wksht2 is ProdCode 1 2 3 Prod1 1.00 .75 .50 Prod2 10.00 8.00 6.00 Prod3 34.00 26.00 18.00 Prod4 18.00 17.00 16.00 Prod5 5.00 4.00 3.00 In worksheet 1 I need to fill in the price for each product by looking up the product code in worksheet 2 then going to the appropriate column (price level) and getting the price where product code and price level intersect. I know there is a formula for this but I can't for the life of me find it? Can someone please help??? Thank you, -- www.bardpv.com Tempe, Arizona |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
index/match/lookup???
One way:
C2: =VLOOKUP(Wksht2!A:D,A2,B2+1,FALSE) In article , Emma Aumack wrote: Here is my dilema . . . On Wksht1 I have the following data Prod Code PriceLevel Price Prod1 1 Prod2 3 Prod3 2 Prod4 3 Prod5 1 In Wksht2 is ProdCode 1 2 3 Prod1 1.00 .75 .50 Prod2 10.00 8.00 6.00 Prod3 34.00 26.00 18.00 Prod4 18.00 17.00 16.00 Prod5 5.00 4.00 3.00 In worksheet 1 I need to fill in the price for each product by looking up the product code in worksheet 2 then going to the appropriate column (price level) and getting the price where product code and price level intersect. I know there is a formula for this but I can't for the life of me find it? Can someone please help??? Thank you, |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
index/match/lookup???
Debra Dalgleish shows how to use =index(match()) for this:
http://www.contextures.com/xlFunctions03.html (Look at example 2) Emma Aumack wrote: Here is my dilema . . . On Wksht1 I have the following data Prod Code PriceLevel Price Prod1 1 Prod2 3 Prod3 2 Prod4 3 Prod5 1 In Wksht2 is ProdCode 1 2 3 Prod1 1.00 .75 .50 Prod2 10.00 8.00 6.00 Prod3 34.00 26.00 18.00 Prod4 18.00 17.00 16.00 Prod5 5.00 4.00 3.00 In worksheet 1 I need to fill in the price for each product by looking up the product code in worksheet 2 then going to the appropriate column (price level) and getting the price where product code and price level intersect. I know there is a formula for this but I can't for the life of me find it? Can someone please help??? Thank you, -- www.bardpv.com Tempe, Arizona -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
index/match/lookup???
I am getting a #VALUE error.
Could you explain this to me? I use Vlookup all the time but don't understand how to get it to look up the value I want in a table using a column value. -- www.bardpv.com Tempe, Arizona "JE McGimpsey" wrote: One way: C2: =VLOOKUP(Wksht2!A:D,A2,B2+1,FALSE) In article , Emma Aumack wrote: Here is my dilema . . . On Wksht1 I have the following data Prod Code PriceLevel Price Prod1 1 Prod2 3 Prod3 2 Prod4 3 Prod5 1 In Wksht2 is ProdCode 1 2 3 Prod1 1.00 .75 .50 Prod2 10.00 8.00 6.00 Prod3 34.00 26.00 18.00 Prod4 18.00 17.00 16.00 Prod5 5.00 4.00 3.00 In worksheet 1 I need to fill in the price for each product by looking up the product code in worksheet 2 then going to the appropriate column (price level) and getting the price where product code and price level intersect. I know there is a formula for this but I can't for the life of me find it? Can someone please help??? Thank you, |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
index/match/lookup???
Price level 1 is the second column, so if the first data row's price
level is in B2, then the column to return is B2+1 Likewise, if price level 3, the value in the 4th column should be returned. In article , Emma Aumack wrote: I am getting a #VALUE error. Could you explain this to me? I use Vlookup all the time but don't understand how to get it to look up the value I want in a table using a column value. -- www.bardpv.com Tempe, Arizona "JE McGimpsey" wrote: One way: C2: =VLOOKUP(Wksht2!A:D,A2,B2+1,FALSE) In article , Emma Aumack wrote: Here is my dilema . . . On Wksht1 I have the following data Prod Code PriceLevel Price Prod1 1 Prod2 3 Prod3 2 Prod4 3 Prod5 1 In Wksht2 is ProdCode 1 2 3 Prod1 1.00 .75 .50 Prod2 10.00 8.00 6.00 Prod3 34.00 26.00 18.00 Prod4 18.00 17.00 16.00 Prod5 5.00 4.00 3.00 In worksheet 1 I need to fill in the price for each product by looking up the product code in worksheet 2 then going to the appropriate column (price level) and getting the price where product code and price level intersect. I know there is a formula for this but I can't for the life of me find it? Can someone please help??? Thank you, |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
index/match/lookup???
try maybe this...
on Wksht1 say on cell along row of data... A2=Prod1 B2=1 eg. cell C2 =SUMPRODUCT((WkSht2!$A$2:$A$6=$A2)*($B2=WkSht2!$B$ 1:$D$1),WkSht2!$B$2:$D$6) on Wksht2!$B$1:$D$1 is the price code 1,2,3. ProdCode 1 2 3 on Wksht2!$A$2:$D$6 is the price list Prod1 1.00 .75 .50 Prod2 10.00 8.00 6.00 Prod3 34.00 26.00 18.00 Prod4 18.00 17.00 16.00 Prod5 5.00 4.00 3.00 -- ***** birds of the same feather flock together.. "Emma Aumack" wrote: Here is my dilema . . . On Wksht1 I have the following data Prod Code PriceLevel Price Prod1 1 Prod2 3 Prod3 2 Prod4 3 Prod5 1 In Wksht2 is ProdCode 1 2 3 Prod1 1.00 .75 .50 Prod2 10.00 8.00 6.00 Prod3 34.00 26.00 18.00 Prod4 18.00 17.00 16.00 Prod5 5.00 4.00 3.00 In worksheet 1 I need to fill in the price for each product by looking up the product code in worksheet 2 then going to the appropriate column (price level) and getting the price where product code and price level intersect. I know there is a formula for this but I can't for the life of me find it? Can someone please help??? Thank you, -- www.bardpv.com Tempe, Arizona |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
index/match/lookup???
Worked Great!!!! Thank you!!!
-- www.bardpv.com Tempe, Arizona "Dave Peterson" wrote: Debra Dalgleish shows how to use =index(match()) for this: http://www.contextures.com/xlFunctions03.html (Look at example 2) Emma Aumack wrote: Here is my dilema . . . On Wksht1 I have the following data Prod Code PriceLevel Price Prod1 1 Prod2 3 Prod3 2 Prod4 3 Prod5 1 In Wksht2 is ProdCode 1 2 3 Prod1 1.00 .75 .50 Prod2 10.00 8.00 6.00 Prod3 34.00 26.00 18.00 Prod4 18.00 17.00 16.00 Prod5 5.00 4.00 3.00 In worksheet 1 I need to fill in the price for each product by looking up the product code in worksheet 2 then going to the appropriate column (price level) and getting the price where product code and price level intersect. I know there is a formula for this but I can't for the life of me find it? Can someone please help??? Thank you, -- www.bardpv.com Tempe, Arizona -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
