Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,624
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,624
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 740
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 06:04 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"