Using V or H lookup
Hi,
I have a chart which is a price list. in one column I have the size of the product, next to it is a column with the product name, and next to it is a column with its price. I want to do a form which looks up a product and gives me the price. Only problem is that we have alot of the same product names and sizes. How can I have excel look it up. I thought maybe combining the size and product columns but that would mess up my Price list. Example: 12oz Whole Jal Price 26oz Whole Jal Price 12oz Sliced Jal Price 26oz Sliced Jal Price Can anyone help me? |
Using V or H lookup
You could combine the size and product in a helper column which is
some way to the right of your price list (eg column Z), so that it won't be seen. Then you could use an INDEX/MATCH combination to get the Price. But anyway, what is wrong with a price list like this: Whole Jal 12oz Price Whole Jal 26oz Price Sliced Jal 12oz Price Sliced Jal 26oz Price ? Hope this helps. Pete On Apr 22, 4:47*pm, katiapro93 wrote: Hi, I have a chart which is a price list. *in one column I have the size of the product, next to it is a column with the product name, and next to it is a column with its price. *I want to do a form which looks up a product and gives me the price. *Only problem is that we have alot of the same product names and sizes. *How can I have excel look it up. *I thought maybe combining the size and product columns but that would mess up my Price list. Example: 12oz * * * *Whole Jal * * *Price 26oz * * * *Whole Jal * * *Price 12oz * * * *Sliced Jal * * *Price 26oz * * * *Sliced Jal * * *Price Can anyone help me? |
Using V or H lookup
Thanks, this is getting me closer to what I want, I can combine the 2 columns
as a helper but then I have never used the Index/Match functions, can you go into it a little for me. I know I can look it up under help but I figured you would get me straight to the point. "Pete_UK" wrote: You could combine the size and product in a helper column which is some way to the right of your price list (eg column Z), so that it won't be seen. Then you could use an INDEX/MATCH combination to get the Price. But anyway, what is wrong with a price list like this: Whole Jal 12oz Price Whole Jal 26oz Price Sliced Jal 12oz Price Sliced Jal 26oz Price ? Hope this helps. Pete On Apr 22, 4:47 pm, katiapro93 wrote: Hi, I have a chart which is a price list. in one column I have the size of the product, next to it is a column with the product name, and next to it is a column with its price. I want to do a form which looks up a product and gives me the price. Only problem is that we have alot of the same product names and sizes. How can I have excel look it up. I thought maybe combining the size and product columns but that would mess up my Price list. Example: 12oz Whole Jal Price 26oz Whole Jal Price 12oz Sliced Jal Price 26oz Sliced Jal Price Can anyone help me? |
Using V or H lookup
Okay, I'll have to make some assumptions about where your data is
located. Assume that the three columns of your pricelist are on a sheet called Prices in columns A to C, and that in Z2 of that sheet you have this formula: =A2&B2 copied down. Then assume that you use E2 to enter the size and F2 to enter the product of the item you are interested in and that you want the price returned to G2. Put this formula in G2: =INDEX(Prices!C:C,MATCH(E2&F2,Prices!Z:Z,0)) E2, F2 and G2 could be in a different sheet. Hope this helps. Pete On Apr 22, 6:06*pm, katiapro93 wrote: Thanks, this is getting me closer to what I want, I can combine the 2 columns as a helper but then I have never used the Index/Match functions, can you go into it a little for me. *I know I can look it up under help but I figured you would get me straight to the point. "Pete_UK" wrote: You could combine the size and product in a helper column which is some way to the right of your price list (eg column Z), so that it won't be seen. Then you could use an INDEX/MATCH combination to get the Price. But anyway, what is wrong with a price list like this: Whole Jal 12oz * * * *Price Whole Jal 26oz * * * *Price Sliced Jal 12oz * * * *Price Sliced Jal 26oz * * * *Price ? Hope this helps. Pete On Apr 22, 4:47 pm, katiapro93 wrote: Hi, I have a chart which is a price list. *in one column I have the size of the product, next to it is a column with the product name, and next to it is a column with its price. *I want to do a form which looks up a product and gives me the price. *Only problem is that we have alot of the same product names and sizes. *How can I have excel look it up. *I thought maybe combining the size and product columns but that would mess up my Price list. Example: 12oz * * * *Whole Jal * * *Price 26oz * * * *Whole Jal * * *Price 12oz * * * *Sliced Jal * * *Price 26oz * * * *Sliced Jal * * *Price Can anyone help me?- Hide quoted text - - Show quoted text - |
All times are GMT +1. The time now is 07:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com